[GENERAL] WARNING: some databases have not been vacuumed in 1953945422 transactions
Hello, we are using PostgreSQL 8.0.3 and have two databases in one cluster. db1 and db2. Each night a shell script is being executed. vacuumdb --analyze -U cmduser db1 vacuumdb --analyze -U cmduser db2 The last weeks the following warnings are given out: WARNING: some databases have not been vacuumed in 1953945422 transactions HINT: Better vacuum them within 193538225 transactions, or you may have a wraparound failure. Now I made the sql-statement: SELECT datname, age(datfrozenxid) FROM pg_database; Yesterday I got this result: datname age db1 1.090.080.531 db2 1.940.858.511 template1 1.940.858.511 template0 1.940.858.511 Today I got the following result: datname age db1 1.075.558.667 db2 1.075.513.031 template1 1.955.716.521 template0 1.955.716.521 Why are there changes of the databases template1 and template0 ?!? Is this critical? Regards Michaela
[GENERAL] Option to undo last update on table.
Is there any option in PGSQL to undo last changes done on a table? Any feature similar to FlashBack Query in Oracle. -- View this message in context: http://www.nabble.com/Option-to-undo-last-update-on-table.-tf3232456.html#a8981518 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
Walter Vaughan wrote: Vladimir Zelinski wrote: I'm struggling to create a stored procedure. I searched on Internet for several hours trying to find a simple example, but didn't find anything. I saw dozens of questions how to create a procedure without any responses. I searched on postgreSql site and found a topic Stored Procedure Example. But actually, they showed how to write a function on postgreSql database. This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. Did you try using pgAdminIII to create your procedure and see if it works any different a creating an identical function? pgAdmin defines a stored procedure as: - A function on EnterpriseDB 8.0 or above, written in edbspl. or - A function written in EnterpriseDB or PostgreSQL 8.1 or above with OUT or INOUT arguments. The separation (in pgAdmin) is historic, and comes from EnterpriseDB which treats the two object types more distinctly for Oracle compatibility (and had SPs before PostgreSQL). Regards, Dave. ---(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] Installing on weendoze vista.
Paul Lambert wrote: Has anyone had any success installing on weendoze vista? Any install I try gets as far as the service user details, if I ask it to create a user it fails, if I specify an existing user account it complains about the user not having enough access - even when said user account is put into the administrator group. I'm assuming PG hasn't been certified under vista yet? If this is correct, is there any plan to do so? Has anyone tried it under server longhorn? Yes, it is planned, but will take some major overhauling of the installer. I haven't tried Longhorn, but I imagine it'll have the same PITA excuses for security as Vista does. In the meantime, you can temporarily disable UAC (User Account Control) to install PostgreSQL. Once installed, it can be re-enabled. There's an option in the Users control panel applet to do this iirc. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PGSQL 8.2.3 Installation problem
On Thu, Feb 15, 2007 at 01:18:25AM -0300, marcelo Cortez wrote: Paul Thanks for your time. the installer log say: fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory C:/Archivos de programa: File exists initdb: removing contents of data directory C:/Archivos de programa/PostgreSQL/8.2/data note path is truncated in message ??? file not exists i delete directory one more time thanks This sounds like a permissions problem on either c:\ or c:\archivos de programa. Note that the account must have specific permissions there *not* granted through the user of Administrators or Power Users, because those permissions are given up before it tries to access them. //Magnus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Stored Procedure examples
Dave Page wrote: pgAdmin defines a stored procedure as: - A function on EnterpriseDB 8.0 or above, written in edbspl. Why does EnterpriseDB determine what is a stored procedure in PostgreSQL? Shouldn't that be limited to their own version of pgAdmin? - A function written in EnterpriseDB or PostgreSQL 8.1 or above with OUT or INOUT arguments. What sense does that make? The separation (in pgAdmin) is historic, and comes from EnterpriseDB which treats the two object types more distinctly for Oracle compatibility (and had SPs before PostgreSQL). The SQL standard does define procedures distinct from functions, and we might want to add that someday, so that system isn't going to make sense anymore. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] user input during runtime
Hello All, I want to prompt user to input some value and do some action on that value in runtime of a sql script. Is there any psql command to do this ?? I can use \echo do display massage but to take input what is the command? Thanks in advance With Regards Ashish Karalkar
Re: [GENERAL] Stored Procedure examples
Peter Eisentraut wrote: Dave Page wrote: pgAdmin defines a stored procedure as: - A function on EnterpriseDB 8.0 or above, written in edbspl. Why does EnterpriseDB determine what is a stored procedure in PostgreSQL? Shouldn't that be limited to their own version of pgAdmin? The community version of pgAdmin has always supported EnterpriseDB features. Are you trying to say we should not do that? And of course, EDB DevStudio does also support SPs (and conversely, community PostgreSQL). - A function written in EnterpriseDB or PostgreSQL 8.1 or above with OUT or INOUT arguments. What sense does that make? The spec says that a procedure may have IN, OUT and INOUT parameters, whilst a function only has IN parameters. It also says that a procedure doesn't return a value whereas a function does. Because PostgreSQL allows return values and IN/OUT/INOUT parameters on the same routine, we use the first part of the definition only when making our distinction. Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09 The separation (in pgAdmin) is historic, and comes from EnterpriseDB which treats the two object types more distinctly for Oracle compatibility (and had SPs before PostgreSQL). The SQL standard does define procedures distinct from functions, and we might want to add that someday, so that system isn't going to make sense anymore. Eh? the spec defines them separately, as does pgAdmin, so if PostgreSQL does it as well pgAdmin's system will no longer make sense? Sure we might have to change how the two are distinguished in future versions, but that's no different from other changes we have to make from version to version. I doubt it's likely to be anything like as much work as the operator family reshuffle has been. Regards, Dave. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] missing FROM-clause
MaRCeLO PeReiRA wrote: Hi guys, I upgraded my PostgreSQL server (7.4 to 8.2) and now all my reports refuse to run because the warning missing FROM-clause. How can I disable it, just to run as the old version?? I have tried: # set add_missing_from to false but, without success!! :( set add_missing_from to true; You can also change this in the bottom of the postgresql.conf file. Then go through your code and fix the queries that require this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
Dave Page wrote: Because PostgreSQL allows return values and IN/OUT/INOUT parameters on the same routine, we use the first part of the definition only when making our distinction. Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09 That same clause also contains various arguments against pgAdmin's definition. For example, all procedures must be invoked using the CALL statement, which PostgreSQL doesn't have. But that is not the point. If you were writing sqlAdmin, then I'd say you are right. But in PostgreSQL we have made conscious efforts to present all programming interfaces under a uniform function label, so I think it does users a disservice if the GUI handles it differently. For that matter, what is supposed to be the practical benefit of this distinction? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] User privilege information.
Hello, How do I find out the privileges of a particular user? (names and ids of the objects and their permissions the user has access to) If there a single table/view that can give this information - please point me to it. Otherwise - give the names of the tables/views that contain this information. Cheers sqlcatz _ Catch all the cricketing action right here. Live score, match reports, photos et al. http://content.msn.co.in/Sports/Cricket/Default.aspx ---(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] Stored Procedure examples
Peter Eisentraut wrote: Dave Page wrote: Because PostgreSQL allows return values and IN/OUT/INOUT parameters on the same routine, we use the first part of the definition only when making our distinction. Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09 That same clause also contains various arguments against pgAdmin's definition. For example, all procedures must be invoked using the CALL statement, which PostgreSQL doesn't have. But that is not the point. If you were writing sqlAdmin, then I'd say you are right. But in PostgreSQL we have made conscious efforts to present all programming interfaces under a uniform function label, so I think it does users a disservice if the GUI handles it differently. For that matter, what is supposed to be the practical benefit of this distinction? As I said, it's a historical design that came about when EDB first introduced stored procedures. pgAdmin maintained the distinction mainly because many users coming from other DBMSs seem to get confused by the whole functions/SPs thing. I believe our interpretation of the distinction is valid, but I'm neither for or against making that distinction as I can see both sides of the argument from the user perspective. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] gmake Error /libpython2.4.a: could not read symbols: Bad value with ./configure --with-python
Dear list members, I am trying to install Postgresql-8.2.3 on a Suse Linux Enterprise Server 10.1 64-Bit with Python 2.4.4. At the gmake command I get 2 errors (see below). I ran: ./configure --with-python --with-openssl gmake Here are the last lines of the gmake process: --- gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplpython.so.0 plpython.o -L/usr/local/lib/python2.4/config -L../../../src/port -lpython2.4 -lpthread -ldl -lutil -lm -Wl,-rpath,'/usr/local/lib/python2.4/config' -o libplpython.so.0.0 /usr/lib64/gcc/x86_64-suse-linux/4.1.0/../../../../x86_64-suse-linux/bin/ld: /usr/local/lib/python2.4/config/libpython2.4.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/python2.4/config/libpython2.4.a: could not read symbols: Bad value collect2: ld returned 1 exit status gmake[3]: *** [libplpython.so.0.0] Error 1 gmake[3]: Leaving directory `/usr/local/src/postgresql-8.2.0/src/pl/plpython' gmake[2]: *** [all] Error 1 gmake[2]: Leaving directory `/usr/local/src/postgresql-8.2.0/src/pl' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/local/src/postgresql-8.2.0/src' gmake: *** [all] Error 2 --- My system: + Suse Linux Enterprise Server 10.1 64-Bit + Postgresql-8.2.3 + Python 2.4.4 Any idea, what's going wrong here? Thanks in advance, Nico ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Option to undo last update on table.
RPK wrote: Is there any option in PGSQL to undo last changes done on a table? Sure -- you just issue ROLLBACK to end your transaction instead of COMMIT. Any feature similar to FlashBack Query in Oracle. I don't know about that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Stored Procedure examples
Dave Page wrote: Peter Eisentraut wrote: Dave Page wrote: Because PostgreSQL allows return values and IN/OUT/INOUT parameters on the same routine, we use the first part of the definition only when making our distinction. Source: section 4.27, SQL-invoked Routines in SWD-02-Foundation-2003-09 That same clause also contains various arguments against pgAdmin's definition. For example, all procedures must be invoked using the CALL statement, which PostgreSQL doesn't have. But that is not the point. If you were writing sqlAdmin, then I'd say you are right. But in PostgreSQL we have made conscious efforts to present all programming interfaces under a uniform function label, so I think it does users a disservice if the GUI handles it differently. For that matter, what is supposed to be the practical benefit of this distinction? As I said, it's a historical design that came about when EDB first introduced stored procedures. pgAdmin maintained the distinction mainly because many users coming from other DBMSs seem to get confused by the whole functions/SPs thing. I believe our interpretation of the distinction is valid, but I'm neither for or against making that distinction as I can see both sides of the argument from the user perspective. FWIW I've seen several reports of users confused because their functions appeared under stored procedures, and other users confused because some of their stored procedures were being categorized as functions while others as stored procedures. It's good to know the reason for this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Option to undo last update on table.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/15/07 02:29, RPK wrote: Is there any option in PGSQL to undo last changes done on a table? Presumably, this is *after* you committed them? Any feature similar to FlashBack Query in Oracle. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1G+/S9HxQb37XmcRAlcTAJ9iBZOJEAUJzMuCaXtSzL8+zYMqQgCeIfCF 3MhJ7OI1P5ZurrgNZC7NZhc= =3IUs -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] gmake Error /libpython2.4.a: could not read symbols: Bad value with ./configure --with-python
Nico Grubert escribió: Dear list members, I am trying to install Postgresql-8.2.3 on a Suse Linux Enterprise Server 10.1 64-Bit with Python 2.4.4. At the gmake command I get 2 errors (see below). /usr/lib64/gcc/x86_64-suse-linux/4.1.0/../../../../x86_64-suse-linux/bin/ld: /usr/local/lib/python2.4/config/libpython2.4.a(abstract.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC /usr/local/lib/python2.4/config/libpython2.4.a: could not read symbols: Bad value collect2: ld returned 1 exit status Did you compile Python manually? This error looks similar to problems I've gotten when I've messed up trying to link 32bit libs with 64 bit objects. I'd suggest installing the Python libs that come with SLES. Does it not ship 2.4.4? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] User privilege information.
On Thu, Feb 15, 2007 at 05:02:44PM +0530, Alexi Gen wrote: How do I find out the privileges of a particular user? (names and ids of the objects and their permissions the user has access to) See the has_object_privilege functions and the system catalogs: http://www.postgresql.org/docs/8.2/interactive/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE http://www.postgresql.org/docs/8.2/interactive/catalogs.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Recover anything from dropped database?
I have a backup as of last night, but I'd like to recover something more recent if I can. If I'm stuck with the backup, I can just stop the postmaster, drop the whole PG directory into place from the backup, and restart, yes? I presume from the near-deafening silence there's nothing else I can do, which is no surprise, but I'd still like confirmation about how to restore the backup. (It turns out I can recover the changes since the backup annother way, since they all happened through interaction with CGI scripts, luckily GET rather than POST - I can replay the relevant URLs grepped from the web server log.) Still, I'm trying to figure out how to avoid my screw-up in the future. I'm not that familiar with the permissions stuff - is there any way to take away drop DB privileges from a user, or even all users? Again, this is PG 7.4. Thanks. - John Burger MITRE ---(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] WARNING: some databases have not been vacuumed in 1953945422 transactions
MG [EMAIL PROTECTED] writes: Each night a shell script is being executed. vacuumdb --analyze -U cmduser db1 vacuumdb --analyze -U cmduser db2 You need to hit template1 every so often, too. You probably might as well just do that every night; it won't take long. The last weeks the following warnings are given out: WARNING: some databases have not been vacuumed in 1953945422 = transactions Is this critical? Yes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Recover anything from dropped database?
John D. Burger wrote: I have a backup as of last night, but I'd like to recover something more recent if I can. If I'm stuck with the backup, I can just stop the postmaster, drop the whole PG directory into place from the backup, and restart, yes? I presume from the near-deafening silence there's nothing else I can do, which is no surprise, but I'd still like confirmation about how to restore the backup. (It turns out I can recover the changes since the backup annother way, since they all happened through interaction with CGI scripts, luckily GET rather than POST - I can replay the relevant URLs grepped from the web server log.) Still, I'm trying to figure out how to avoid my screw-up in the future. I'm not that familiar with the permissions stuff - is there any way to take away drop DB privileges from a user, or even all users? Again, this is PG 7.4. AFAIK, once a database is dropped, it's gone. That's kinda the point in dropping it. So, yeah, you're backup is the way to go. Also, since you're going to have start from a fresh restore of your backup, I'd recommend upgrading to a more recent version of Postgres, at least 8.1.x if not the most recent which is 8.2.3. As far as your questions regarding limiting users' permissions, read the chapter on Database Roles and Priveledges from the docs: http://www.postgresql.org/docs/8.2/interactive/user-manag.html ---(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] Constraint enforcement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Is there any documentation other than the soruce code on how postgres performs the appropriate (PK, FK, CHECK, UNIQUE) constraint enforcement on DELETEs, UPDATEs, and INSERTs? What I'm trying to figure out is how postgre identifies the *exact* set of constraints to enforce for each of the different commands above, and the order in which these are performed (since some checks are way more expensive than others). Thank you, Demian -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFF1IcYWs7G5iIp9akRAgM4AKChLgEMZRJC36hpEPDtPBPjrVfFJwCfSe43 B1KNLqOsn5qQnbXPnup2n7A= =kABQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] postgresql 8.2 Installation error at gmake
Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Thanks a lot! Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib PASS! Step2 gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing zic.o ialloc.o scheck.o localtime.o -L../../src/port -Wl,-rpath,'/local/postgresql/lib' -lpgport -lcrypt -ldl -lm -o zic zic.o(.text+0xb5f): In function `associate': zic.c: undefined reference to `pg_qsort' zic.o(.text+0x2c54): In function `writezone': zic.c: undefined reference to `pg_qsort' collect2: ld returned 1 exit status gmake[2]: *** [zic] Error 1 gmake[2]: Leaving directory `/postgresql/src/timezone' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/postgresql/src' gmake: *** [all] Error 2 FAIL? ---(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] Constraint enforcement
Demian Lessa [EMAIL PROTECTED] writes: What I'm trying to figure out is how postgre identifies the *exact* set of constraints to enforce for each of the different commands above, and the order in which these are performed (since some checks are way more expensive than others). Triggers on the same event are fired in alphabetical order by tgname. I don't believe any particular ordering is enforced among CHECK constraints --- but they all happen before any triggers fire. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql 8.2 Installation error at gmake
Emi Lu wrote: Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir does not do what you seem to think. Remove it, because it's going to cause other problems further on. Also, if you need --disable-spinlocks, your installation is not going to perform very well; did you try without that? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Procedure examples
Dave Page [EMAIL PROTECTED] writes: ... I doubt it's likely to be anything like as much work as the operator family reshuffle has been. [ blink... ] I would not have thought that the opfamily stuff would affect pgAdmin at all. Would you mind clarifying what problems it caused you? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresql 8.2 Installation error at gmake
Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir does not do what you seem to think. Remove it, because it's going to cause other problems further on. Also, if you need --disable-spinlocks, your installation is not going to perform very well; did you try without that? I am not super user for this Linux OS! I can only access my directories. Specified --datadir=/postgreSQL_data because my previous version 8.0.0 DATA was there (which is not used anymore) I do not care the spinlocks for now. Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql 8.2 Installation error at gmake
Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir does not do what you seem to think. Remove it, because it's going to cause other problems further on. Also, if you need --disable-spinlocks, your installation is not going to perform very well; did you try without that? I am not super user for this Linux OS! I can only access my directories. Specified --datadir=/postgreSQL_data because my previous version 8.0.0 DATA was there (which is not used anymore) Also tried ./configure --prefix=/local/postgresql --without-docdir --without-readline --without-zlib PASS But gmake failed as well! gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing zic.o ialloc.o scheck.o localtime.o -L../../src/port -Wl,-rpath,'/local/postgresql/lib' -lpgport -lcrypt -ldl -lm -o zic zic.o(.text+0xb5f): In function `associate': zic.c: undefined reference to `pg_qsort' zic.o(.text+0x2c54): In function `writezone': zic.c: undefined reference to `pg_qsort' collect2: ld returned 1 exit status gmake[2]: *** [zic] Error 1 gmake[2]: Leaving directory `/nfs/home/e/u/postgresql/src/timezone' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/nfs/home/e/u/postgresql/src' gmake: *** [all] Error 2 Thanks ! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Recover anything from dropped database?
John D. Burger wrote: Still, I'm trying to figure out how to avoid my screw-up in the future. The good old don't do everything as root comes to mind. I'm not that familiar with the permissions stuff - is there any way to take away drop DB privileges from a user, or even all users? Again, this is PG 7.4. There is no specific drop database privilege, but only owners or superusers can drop things. So do your general transactional operations as some other user. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] backup database by cloning itself
On 13 Feb, 14:54, filippo [EMAIL PROTECTED] wrote: My target is to have the backup operation not affecting the users, so I want to be able to copy a database even if the database is used by someone. I could use pg_dump/pg_restore. pg_dump doesn't have to have exclusive access to database to perform the operation. My only problem is that pg_dump create a backup on a file, the best to me whould be to have a perfect clone (users/ data etc) of original database ready to be used just after the cloning. Is it possible? Thanks, Filippo ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresql 8.2 Installation error at gmake
Emi Lu wrote: Hello, Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir does not do what you seem to think. Remove it, because it's going to cause other problems further on. Also, if you need --disable-spinlocks, your installation is not going to perform very well; did you try without that? I am not super user for this Linux OS! I can only access my directories. Specified --datadir=/postgreSQL_data because my previous version 8.0.0 DATA was there (which is not used anymore) You're confusing the datadir as understood by configure with the PGDATA dir that's created by initdb. Don't. Leave the --datadir option out. I do not care the spinlocks for now. Configure will work anyway without the --disable-spinlock; you're wasting your time with that option. Anyway, please note that your linker problem does not seem to be related to any of these options. Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Constraint enforcement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks, Tom. This doesn't quite answer the case in which, say, an UPDATE is performed and the underlying table has a . OK, a piece of the puzzle is in place- all CHECKs are performed before all TRIGGERs. But what is the actual order of constraint enforcement, considering PKs, FKs, UNIQUEs, CHECKs, COLUMN TYPES/DOMAINs, NOT NULLs, and TRIGGERs? Are all constraints always enforced, or does postgres always find the *exact* set of constraints it needs to check for the specific command? Take the very simplistic example below: UPDATE foo SET name='foo', tag='bar' WHERE name='joe'; and UPDATE foo SET name='foo' WHERE name='joe'; Assuming some constraints on column tag, and PK name, will both commands trigger the same sets of constraint enforcements, or will the second command trigger only the *necessary* checks, given that the tag column is not modified? In what order are PK, TYPE, NOT NULLs, etc checked on name? Are the constraints on tag ever checked on the second command? Thank you, Demian Tom Lane wrote: Demian Lessa [EMAIL PROTECTED] writes: What I'm trying to figure out is how postgre identifies the *exact* set of constraints to enforce for each of the different commands above, and the order in which these are performed (since some checks are way more expensive than others). Triggers on the same event are fired in alphabetical order by tgname. I don't believe any particular ordering is enforced among CHECK constraints --- but they all happen before any triggers fire. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFF1JfNWs7G5iIp9akRAgEQAJ9IWPgaxUNzwymucc8pqIdAEPM/GgCfYqSv 1PCKTVtXlwCW33M4532nYm0= =1cFT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Adding audit trail fields and triggers to all tables
I need to add the following fields to all tables to existing 8.1 database: created timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, createdby text DEFAULT CURRENT_USER, updated timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, updatedby text -- current_user who last updates this record I need also to add triggers so that updated and updatedby fields ae automatically set when record is changed. Where to find script which implements this ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] backup database by cloning itself
On 14 Feb, 08:33, [EMAIL PROTECTED] (Ron Johnson) wrote: -BEGIN PGP SIGNED MESSAGE- There's almost definitely a better way to do what you want to do. What benefit are you trying to obtain by creating 720 almost identical databases per month? I only need the last 24, overwriting each day. In my case 8 are enough (8am to 24pm, one every 2 hours). The important thing is to be able to open each very simply (read only) without restore anything. Just tell my application (written be me), to point the basckup database instead of latest one. Have you tried pg_dump? probably this is ok. pg_dump create a backup file, not a true clone db. I want a exact copy (data and users and everithing) with a different name (05-mydatabase 06-mydatabase ...) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] clarififcation
Hai, I started working on postgresql, I want to know how to write the connect by clause of oracle. My problem is i have a table with id, parent id. In same table i will insert the record with parent id as previous row id. I want fetch the records in the hierarchy. how do i do it. Pls help me. I want to get in touch with developers and then i need to try multi master replication also. Regards, Gayathri Send instant messages to your online friends http://uk.messenger.yahoo.com
Re: [GENERAL] backup database by cloning itself
13 Feb 2007 05:54:44 -0800, filippo [EMAIL PROTECTED]: Hello, my database is not very big so I want to adopt this backup strategy: I want to clone my database every 1 hour to another database 'currenttime_mydatabase' in order to have 24 backup a day, overwriting the yesterday backups by today-same-time backups. This is good for me because I have all the backups readily available to be read by my program (opening the backup read only). This is a very important for my needs. I'm writing a script run by cron each hour to do accomplish the backup task. My target is to have the backup operation not affecting the users, so I want to be able to copy a database even if the database is used by someone. Can I use CREATE DATABASE my_backup_database TEMPLATE current_database? no. database used as template must not be accessed during copy Is there a better way to get what I need? you can script this: pg_dump sourcedb | psql targetdb -- Filip Rembiałkowski ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Union Query Improvement
There is only one table/view, it's getting one record for each of the hundred or so plan ID's that I'm looking for. On 2/13/07 11:29 PM, in article [EMAIL PROTECTED], Ron Johnson [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/13/07 07:46, Ray Bannon wrote: I have a query which is running a bit slowly, and I'm wondering if anyone has a design improvement. Basically it's a series of unions as follows: Select ID, plan_name from table/view Where plan_name = 'A' And rownum = 1 UNION Select ID, plan_name from table/view Where plan_name = 'B' And rownum = 1 UNION Select ID, plan_name from table/view Where plan_name = 'C' And rownum = 1 UNION Ad infinitum for about 100 iterations. Any way to write this more efficiently? Just out of curiosity: why does your(?) design have 100 tables/views with the same (or almost identical) structure? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0rpTS9HxQb37XmcRAtH5AJ4zz5NPM5rBsNWLrKC+/Md6GhxCNgCfZBHf AeMbTRNKp4guK81pGwfU5wc= =t9y+ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
I searched on postgreSql site and found a topic Stored Procedure Example. But actually, they showed how to write a function on postgreSql database. A procedure is a function with a return type of void. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Proper escaping for char(3) string, or PHP at fault, or me at fault?
On Wed, 14 Feb 2007, Chris wrote: semi-ambivalent wrote: All, I have a char(3) column that has occasional values of this: (V) In a PHP-called nested query I've a line something like: select * from tableA where = any (select date from tableA where void ! = '(V)') group by date order by record Shouldn't that be select * from table where FIELD = any (.) ? -- Postgresql php tutorials http://www.designmagick.com/ Yes it should be, and I'm hoping I just made a typo in my post, but I've missed bonehead mistakes like that in the past; it's not forbidden in the future. I'll check after I take my son to school. r ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Protect PL/PGSQL source
Hello, Does anyone knows if it´s possible to protect from users see a PL/PGSQL source ? I have a PL/PGSQL function and I want to make it invisible to users. Just a example, in Oracle there is a option like this: http://www.dbasupport.com/oracle/ora9i/wrap_utility.shtml Regards, Wilton Ruffato Wonrath [EMAIL PROTECTED] São Paulo - Brazil PostgreSQL 8.2 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] User privilege information.
On Feb 15, 6:32 am, [EMAIL PROTECTED] (Alexi Gen) wrote: Hello, How do I find out the privileges of a particular user? (names and ids of the objects and their permissions the user has access to) If there a single table/view that can give this information - please point me to it. Otherwise - give the names of the tables/views that contain this information. Cheers sqlcatz _ Catch all the cricketing action right here. Live score, match reports, photos et al.http://content.msn.co.in/Sports/Cricket/Default.aspx ---(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 Try \z tableName anthony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] massive memory allocation until machine crashes
Hello, given is a postgres database in version PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 and there is a table visit with 26 million tuples using 8 GB of space SELECT relname, reltuples, relpages*8/1024 as relpages_in_MB FROM pg_class ORDER BY relpages DESC limit 10; relname | reltuples | relpages_in_mb --++ visit| 2.6348e+07 | 7673 The table is from ofbiz and for logging accesses to the webapplication. Running a delete command which deletes all but a few tuples causes the postmaster to allocate memory: ---10903 postgres 25 0 214M 213M 10412 R95.3 10.5 6:07 postmaster Until all memory and swap is gone - that was 1.4GB of top:SIZE ---delete from visit where date(created_stamp) date(current_timestamp - '7 days'::interval); I just do not know why it needs allocating so much memory. I solved the problem in dividing the affected tuples in parts and deleting it part by part. ---delete from visit where date(created_stamp) date(current_timestamp - '300 days'::interval); ---delete from visit where date(created_stamp) date(current_timestamp - '240 days'::interval); ---... Why does the postmaster need so much memory to delete tuples? Thanks in advance. Alexander Elgert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Proper escaping for char(3) string, or PHP at fault, or me at fault?
On Wed, 14 Feb 2007, [EMAIL PROTECTED] wrote: On Wed, 14 Feb 2007, Chris wrote: semi-ambivalent wrote: All, I have a char(3) column that has occasional values of this: (V) In a PHP-called nested query I've a line something like: select * from tableA where = any (select date from tableA where void ! = '(V)') group by date order by record Shouldn't that be select * from table where FIELD = any (.) ? -- Postgresql php tutorials http://www.designmagick.com/ Yes it should be, and I'm hoping I just made a typo in my post, but I've missed bonehead mistakes like that in the past; it's not forbidden in the future. I'll check after I take my son to school. r Well, I didn't forget a field (thank goodness), but I let a space creep in in !=, I had ! =. It was on a linewrap in a window but that's no excuse to miss it. (sigh) thx, r ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] clarififcation
gayathri jayapal wrote: Hai, I started working on postgresql, I want to know how to write the connect by clause of oracle. My problem is i have a table with id, parent id. In same table i will insert the record with parent id as previous row id. I want fetch the records in the hierarchy. how do i do it. If you installed from source, look in .../contrib/tablefunc/ for an installable connectby() function. If you installed by package, there is usually one or more additional packages containing the contrib stuff. Pls help me. I want to get in touch with developers and then i need to try multi master replication also. There are no pre-packaged multi-master replication solutions at the moment. The first thing you'll have to do in building such a solution is decide on how you want to deal with conflicting updates. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] backup database by cloning itself
On Feb 14, 2:14 am, filippo [EMAIL PROTECTED] wrote: On 13 Feb, 14:54, filippo [EMAIL PROTECTED] wrote: My target is to have the backup operation not affecting the users, so I want to be able to copy a database even if the database is used by someone. I could use pg_dump/pg_restore. pg_dump doesn't have to have exclusive access to database to perform the operation. My only problem is that pg_dump create a backup on a file, the best to me whould be to have a perfect clone (users/ data etc) of original database ready to be used just after the cloning. Is it possible? Thanks, Filippo Well, I could see you writing a client application that creates a clone by first recreating all the schemas in your database and then copying the data to the clone, and probably quite a bit more, In such a case, since you have absolute control over your client code, you can do anything you want. I am not sure, though, that that is the best use of your time and hardware resources, especially if all you're after is a backup. Just think of all the overhead involved in creating a new clone, and everything that implies, every hour. But why not further explore your backup options if all you're concerned about is a reliable backup. You may find 23.3. On-line backup and point-in-time recovery (PITR) in the postgresql documentation useful. You haven't given any information about why it might not be appropriate in your situation. If you're really doing what it looks to me like you're doing, then you may be in the beginning stages of reinventing Postgresql's PITR capability. The builtin support for PITR in Postgresql strikes me as sufficient for what you say you need. If you require more, which would imply you want more than the simple backup you say you're after, then defining a suitable suite of triggers and audit tables may serve. Neither should adversely affect your users. especially if your database is not very big . HTH Ted ---(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] backup database by cloning itself
filippo wrote: On 14 Feb, 08:33, [EMAIL PROTECTED] (Ron Johnson) wrote: -BEGIN PGP SIGNED MESSAGE- There's almost definitely a better way to do what you want to do. What benefit are you trying to obtain by creating 720 almost identical databases per month? I only need the last 24, overwriting each day. In my case 8 are enough (8am to 24pm, one every 2 hours). The important thing is to be able to open each very simply (read only) without restore anything. Just tell my application (written be me), to point the basckup database instead of latest one. I wonder if you could have a PITR warm standby instead, and every hour stop it and backup that. This is very hand-wavy, you're expected to fill in the details :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] massive memory allocation until machine crashes
Alexander Elgert wrote: Hello, given is a postgres database in version PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 Upgrade to 7.4.16 as soon as is convenient - you're missing 8 sets of bug-fixes. and there is a table visit with 26 million tuples using 8 GB of space The table is from ofbiz and for logging accesses to the webapplication. Running a delete command which deletes all but a few tuples causes the postmaster to allocate memory: ---10903 postgres 25 0 214M 213M 10412 R95.3 10.5 6:07 postmaster Until all memory and swap is gone - that was 1.4GB of top:SIZE Do you have any triggers or foreign keys on this table? If so, each of those will need to be tracked. There may be a memory-leak in 7.4.8 that's since been fixed, probably worth checking the release notes at the end of the manual. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Union Query Improvement
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ever considered using a materialized view? If you do, you could write the code to load/sync your materialized view using a FOR loop. Note that PostgreSQL does not support materialized views out of the box, so you'd need to play around with some triggers and functions. If you're interested, this is a good starting point: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html If you don't change the involved tables all that much, you'll incur in very little overhead for maintaining the materialized view. If you do change them quite a bit, you might wanna take a step back and reconsider parts of your schema design. BTW, are all your table/view the same for each of the SELECTs? I'm assuming not... Demian Ray Bannon wrote: There is only one table/view, it's getting one record for each of the hundred or so plan ID's that I'm looking for. On 2/13/07 11:29 PM, in article [EMAIL PROTECTED], Ron Johnson [EMAIL PROTECTED] wrote: On 02/13/07 07:46, Ray Bannon wrote: I have a query which is running a bit slowly, and I'm wondering if anyone has a design improvement. Basically it's a series of unions as follows: Select ID, plan_name from table/view Where plan_name = 'A' And rownum = 1 UNION Select ID, plan_name from table/view Where plan_name = 'B' And rownum = 1 UNION Select ID, plan_name from table/view Where plan_name = 'C' And rownum = 1 UNION Ad infinitum for about 100 iterations. Any way to write this more efficiently? Just out of curiosity: why does your(?) design have 100 tables/views with the same (or almost identical) structure? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFF1KJyWs7G5iIp9akRArwOAKCSZTDyfxArgLHJSOozmbopALtfaACfURB0 m4s1oSyNqkQjYcx4//AcfTA= =l7AK -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Union Query Improvement
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 02/14/07 06:43, Ray Bannon wrote:
There is only one table/view, it's getting one record for each of the
Clarity in question-asking always helps.
hundred or so plan ID's that I'm looking for.
One table, huh? Then why all the references to ROWNUM = 1? To us
who don't know your system, ROWNUM looks a whole lot like a PK.
Anyway...
SELECT ID, PLAN_NAME
FROM SOME_TABLE
WHERE PLAN_NAME IN ('A', 'B', 'C', etc, etc)
AND ROWNUM = 1l
Making a lookup table with just a list of PLAN_NAME values in it
would also work, but then you'd have to join the lookup table to
SOME_TABLE on PLAN_NAME.
On 2/13/07 11:29 PM, in article [EMAIL PROTECTED], Ron Johnson
[EMAIL PROTECTED] wrote:
On 02/13/07 07:46, Ray Bannon wrote:
I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:
Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION
Ad infinitum for about 100 iterations.
Any way to write this more efficiently?
Just out of curiosity: why does your(?) design have 100 tables/views
with the same (or almost identical) structure?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFF1KJ4S9HxQb37XmcRAs+AAKCad0JDoZgwXXpxXHNvXm/8BG1QRgCeLW+H
bAFQXlbtYfq27z4WDjsKahY=
=4JYC
-END PGP SIGNATURE-
---(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] Adding audit trail fields and triggers to all tables
On Wed, Feb 14, 2007 at 06:38:40PM +0200, Andrus wrote: I need to add the following fields to all tables to existing 8.1 database: Why redo work? http://pgfoundry.org/projects/tablelog/ http://www.postgresql.org/docs/current/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE Cheers, D created timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, createdby text DEFAULT CURRENT_USER, updated timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, updatedby text -- current_user who last updates this record I need also to add triggers so that updated and updatedby fields ae automatically set when record is changed. Where to find script which implements this ? Andrus. ---(end of broadcast)--- TIP 6: explain analyze is your friend -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Database performance comparison paper.
Some people may find this interesting reading. http://us.devloop.org.uk/ - Marc ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Protect PL/PGSQL source
On 2/14/07, Wilton [EMAIL PROTECTED] wrote: Hello, Does anyone knows if it´s possible to protect from users see a PL/PGSQL source ? I have a PL/PGSQL function and I want to make it invisible to users. stored procedure code sits in the pg_proc table as you know. access to pg_proc is not required to run a function: revoke select on pg_proc from public, foo; be aware this breaks psql/pgadmin, etc for that user(s). merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Option to undo last update on table.
In my case, I did an accidental update on a large table where different column values have changed. I continued working and found next day that something wrong happened. Rollback will just rollback to last step (if I am right), but is there a way to bring the table to a certain TimeStamp. Just in case you have not heard of FlashBack facility in Oracle, check this link: http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm Oracle FlashBack Technology I think PGSQL future versions must have something like this. -- View this message in context: http://www.nabble.com/Option-to-undo-last-update-on-table.-tf3232456.html#a8990995 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Stored Procedure examples
On 2/14/07, Martijn van Oosterhout kleptog@svana.org wrote: On Wed, Feb 14, 2007 at 05:18:10PM -0500, Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. Way back when I learned that procedures are merely functions that don't return a value. So in that sense procedures are indeed just functions. You obviously mean something else but I'm not sure what. I thought stored procedures did not run implicitly in transactions like functions do. If that's the case, that's a huge differenceyou could do vacuum, large loads, etc. that you currently have to do outside the database. I'm not sure about this though. merlin ---(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] postgresql 8.2 Installation error at gmake
Hello, Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. (1) \rm -r /local/postgresql (2) ./configure --prefix=/local/postgresql --without-docdir --without-readline --without-zlib The last line for the above configuration is: config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port (3) gmake ERROR shown in the attached file. Thank you! gmake gmake -C doc all gmake[1]: Entering directory `/postgresql-8.2.3/doc' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/postgresql-8.2.3/doc' gmake -C src all gmake[1]: Entering directory `/postgresql-8.2.3/src' gmake -C port all gmake[2]: Entering directory `/postgresql-8.2.3/src/port' echo #define PGBINDIR \/local/postgresql/bin\ pg_config_paths.h echo #define PGSHAREDIR \/local/postgresql/share\ pg_config_paths.h echo #define SYSCONFDIR \/local/postgresql/etc\ pg_config_paths.h echo #define INCLUDEDIR \/local/postgresql/include\ pg_config_paths.h echo #define PKGINCLUDEDIR \/local/postgresql/include\ pg_config_paths.h echo #define INCLUDEDIRSERVER \/local/postgresql/include/server\ pg_config_paths.h echo #define LIBDIR \/local/postgresql/lib\ pg_config_paths.h echo #define PKGLIBDIR \/local/postgresql/lib\ pg_config_paths.h echo #define LOCALEDIR \\ pg_config_paths.h echo #define DOCDIR \\ pg_config_paths.h echo #define MANDIR \/local/postgresql/man\ pg_config_paths.h gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -I../../src/port -DFRONTEND -I../../src/include -D_GNU_SOURCE -c -o path.o path.c ar crs libpgport.a strlcpy.o copydir.o dirmod.o exec.o noblock.o path.o pipe.o pgsleep.o pgstrcasecmp.o qsort.o qsort_arg.o sprompt.o thread.o gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing -I../../src/port -I../../src/include -D_GNU_SOURCE -c path.c -o path_srv.o ar crs libpgport_srv.a strlcpy_srv.o copydir_srv.o dirmod_srv.o exec_srv.o noblock_srv.o path_srv.o pipe_srv.o pgsleep_srv.o pgstrcasecmp_srv.o qsort_srv.o qsort_arg_srv.o sprompt_srv.o thread_srv.o gmake[2]: Leaving directory `/postgresql-8.2.3/src/port' gmake -C timezone all gmake[2]: Entering directory `/postgresql-8.2.3/src/timezone' gmake -C ../../src/port all gmake[3]: Entering directory `/postgresql-8.2.3/src/port' gmake[3]: Nothing to be done for `all'. gmake[3]: Leaving directory `/postgresql-8.2.3/src/port' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing zic.o ialloc.o scheck.o localtime.o -L../../src/port -Wl,-rpath,'/local/postgresql/lib' -lpgport -lcrypt -ldl -lm -o zic zic.o(.text+0xb5f): In function `associate': zic.c: undefined reference to `pg_qsort' zic.o(.text+0x2c54): In function `writezone': zic.c: undefined reference to `pg_qsort' collect2: ld returned 1 exit status gmake[2]: *** [zic] Error 1 gmake[2]: Leaving directory `/postgresql-8.2.3/src/timezone' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/postgresql-8.2.3/src' gmake: *** [all] Error 2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Option to undo last update on table.
On Thursday 15 February 2007 10:30, RPK [EMAIL PROTECTED] wrote: Rollback will just rollback to last step (if I am right), but is there a way to bring the table to a certain TimeStamp. You can use a PITR backup to restore a fresh cluster to a time just before your update. If you are not running PITR backups, then no. -- The power of the executive to cast a man into prison without formulating any charge known to the law, and particularly to deny him the judgment of his peers, is in the highest degree odious, and the foundation of all totalitarian government whether Nazi or Communist. -- Winston Churchill ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Recover anything from dropped database?
On Thu, Feb 15, 2007 at 10:53:48 -0500, John D. Burger [EMAIL PROTECTED] wrote: I presume from the near-deafening silence there's nothing else I can do, which is no surprise, but I'd still like confirmation about how to restore the backup. (It turns out I can recover the changes since the backup annother way, since they all happened through interaction with CGI scripts, luckily GET rather than POST - I can replay the relevant URLs grepped from the web server log.) It wasn't entirely clear what you wanted to accomplish. If you had mentioned needing find at least some of the transactions that occured, then you might have got some suggestions along the lines of imaging the disk to capture data from teh recently freed blocks. There wouldn't be an automated way to get the data back into the database, but you might have been able to find some things out. However, the web server logs are probably going to give you what you want more reliably than grepping through the freed blocks, so there isn't any point in going there. ---(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] Database performance comparison paper.
Excerpt from the document: === 2. What is compared here - Apples and Oranges The setups are as standard as can be. The only principle guiding the installation of all the software is simplicity. No optimization, no tweaks, no editing of configuration files. === That doesn't sound like a very useful methodology for benchmarking. Regards, Shelby Cain - Original Message From: Marc Evans [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, February 15, 2007 12:21:03 PM Subject: [GENERAL] Database performance comparison paper. Some people may find this interesting reading. http://us.devloop.org.uk/ - Marc ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Any questions? Get answers on any topic at www.Answers.yahoo.com. Try it now. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Database performance comparison paper.
On Thursday 15 February 2007 11:29, Shelby Cain [EMAIL PROTECTED] wrote: === 2. What is compared here - Apples and Oranges The setups are as standard as can be. The only principle guiding the installation of all the software is simplicity. No optimization, no tweaks, no editing of configuration files. === That doesn't sound like a very useful methodology for benchmarking. In particular, it means they used MyISAM with no fsync for MySQL. They might as well have sent those inserts to /dev/null, it would have been as useful a test. They also didn't use transactions. -- When we vote for taxes, we are voting to steal from our neighbors ---(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] Database performance comparison paper.
Shelby Cain wrote: Excerpt from the document: === 2. What is compared here - Apples and Oranges The setups are as standard as can be. The only principle guiding the installation of all the software is simplicity. No optimization, no tweaks, no editing of configuration files. === That doesn't sound like a very useful methodology for benchmarking. Thanks for the excerpt Shelby - just saved me reading the report. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored Procedure examples
Merlin Moncure wrote: I thought stored procedures did not run implicitly in transactions like functions do. That has nothing to do with the reality in PostgreSQL. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Database performance comparison paper.
In response to Shelby Cain [EMAIL PROTECTED]: Excerpt from the document: === 2. What is compared here - Apples and Oranges The setups are as standard as can be. The only principle guiding the installation of all the software is simplicity. No optimization, no tweaks, no editing of configuration files. === That doesn't sound like a very useful methodology for benchmarking. The amazing thing is that PostgreSQL still compared favorably, in _spite_ of this obvious configuration bias. I'm going to have to set up a system and compare a properly tuned MySQL to a properly tuned PostgreSQL to see what happens ... -- Bill Moran Collaborative Fusion Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
Tom Lane wrote: Dave Page [EMAIL PROTECTED] writes: ... I doubt it's likely to be anything like as much work as the operator family reshuffle has been. [ blink... ] I would not have thought that the opfamily stuff would affect pgAdmin at all. Would you mind clarifying what problems it caused you? No problems - just changes to the code to allow the user to browse operators and op classes in 8.3, and addition of the new (C++) classes to allow the same for op families. Perfectly normal next-version-support stuff :-) Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] backup database by cloning itself
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 01:14, filippo wrote: On 13 Feb, 14:54, filippo [EMAIL PROTECTED] wrote: My target is to have the backup operation not affecting the users, so I want to be able to copy a database even if the database is used by someone. I could use pg_dump/pg_restore. pg_dump doesn't have to have exclusive access to database to perform the operation. My only problem is that pg_dump create a backup on a file, the best to me whould be to have a perfect clone (users/ data etc) of original database ready to be used just after the cloning. Is it possible? pg_dump | pg_restore. But you still haven't told us why you need copies of the database every 2 hours. What is the business need you are trying to solve. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1MGpS9HxQb37XmcRAlYvAJ92Hl9wI/7mb/zOh1xsZwRHR8uDvQCdFKE2 SIdsgnhecZKpEUMWYARLWWA= =lqeo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Where art thou pg_clog?
We have a production system with multiple identical database instances on the same hardware, with the same configuration, running databases with the exact same schema. They each have different data, but the database sizes and load patterns are almost exactly the same. We are running pg 8.1.5 (upgraded the day before 8.1.6 came out, oh well ;^) and since then we have noticed the following error on two of the servers: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory The first time this happened, I chalked it up to some kind of disk corruption based on the mailing list archives. So I dumped the databases, did a fresh initdb, forced an fsck (these run with a jfs data partition and an ext2 wal partition) which found no problems and then reloaded the databases. Now about a week later I see the same problem on different server. We never saw this problem running 8.1.3 on these same machines over many months, so I'm beginning to get suspect that something we changed since running 8.1.3 is to blame. Before the upgrade these systems ran postgres 8.1.3 and slony 1.1.5. Now they run postgres 8.1.5 and slony 1.2.6 (I don't know that the slony version is important, I add it here for completeness). Nothing else important has changed on these boxes. I see the 8.1.8 is out now, though nothing I see in the release notes seems relevant to this issue. Here are some specific things I'd like to know: 1. Is it possible to fix this problem without an dumpall/initdb/ restore. That takes many hours and can only be done when I'm supposed to be at home relaxing (yeah right) ;^) FWIW, the system is functioning fine right now from what I can tell, save the above errors in the log every few minutes. 2. What more info can I give to figure out the cause of this. Are there files I can inspect to find out more? 3. Is it possible that this is a side-affect of the upgrade to 8.1.5? Thanks for any insights, -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Database performance comparison paper.
Am 15.02.2007 um 11:21 schrieb Marc Evans: http://us.devloop.org.uk/ These *peeep* [deleted] compared MySQL with MyISAM to ACID compliant databases. So why not compare an F-15 to 747? What? Apples and Oranges? So what? You can compare anything you want, right? Only the result matters. So, my hint to these guys is: learn about the principles of databases (at least read: http://en.wikipedia.org/wiki/ACID), then about the principles of optimizing databases, then about the principles of testing (don't compare products or setups that do completely different things) and then do you homework again. Go home. cug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where art thou pg_clog?
Casey Duncan wrote: We have a production system with multiple identical database instances on the same hardware, with the same configuration, running databases with the exact same schema. They each have different data, but the database sizes and load patterns are almost exactly the same. We are running pg 8.1.5 (upgraded the day before 8.1.6 came out, oh well ;^) and since then we have noticed the following error on two of the servers: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory Can you relate it to autovacuum? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Where art thou pg_clog?
Casey Duncan wrote: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory The first time this happened, I chalked it up to some kind of disk corruption based on the mailing list archives. So I dumped the databases, did a fresh initdb, forced an fsck (these run with a jfs data partition and an ext2 wal partition) which found no problems and then reloaded the databases. Now about a week later Unless you actually executed 2565134864 transactions in that one week, this is still data corruption. Check for faulty memory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Database performance comparison paper.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/15/07 15:29, Guido Neitzer wrote: Am 15.02.2007 um 11:21 schrieb Marc Evans: http://us.devloop.org.uk/ These *peeep* [deleted] compared MySQL with MyISAM to ACID compliant databases. So why not compare an F-15 to 747? What? Apples and Oranges? So what? You can compare anything you want, right? Only the result matters. Bad analogy. Both the F-15 and 747 are high-performance (within their problem domains) and have redundancy out the wazoo. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1NVyS9HxQb37XmcRAubPAKDUOQ6n38YnGWhZTIHZM3zyTDFBDQCfYvyn 3Wdim4mnuFXn0hIPEHGu5Vw= =nvPe -END PGP SIGNATURE- ---(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] postgresql 8.2 Installation error at gmake
Tried to install 8.2, configuration passed, but when running gmake, got the following error, any clues? Please provide more details -- what operating system is this? i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 Step1 ./configure --prefix=/local/postgresql --datadir=/postgreSQL_data --without-docdir --without-readline --disable-spinlocks --without-zlib Please note that --datadir does not do what you seem to think. Remove it, because it's going to cause other problems further on. Also, if you need --disable-spinlocks, your installation is not going to perform very well; did you try without that? I am not super user for this Linux OS! I can only access my directories. Specified --datadir=/postgreSQL_data because my previous version 8.0.0 DATA was there (which is not used anymore) You're confusing the datadir as understood by configure with the PGDATA dir that's created by initdb. Don't. Leave the --datadir option out. I do not care the spinlocks for now. Configure will work anyway without the --disable-spinlock; you're wasting your time with that option. Anyway, please note that your linker problem does not seem to be related to any of these options. Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. All right. I know how it caused the problem for me. My gcc was not setup correctly. After changing the gcc, it works for me now :). Thank you for all your help! ---(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] Where art thou pg_clog?
On Feb 15, 2007, at 1:50 PM, Peter Eisentraut wrote: Casey Duncan wrote: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory The first time this happened, I chalked it up to some kind of disk corruption based on the mailing list archives. So I dumped the databases, did a fresh initdb, forced an fsck (these run with a jfs data partition and an ext2 wal partition) which found no problems and then reloaded the databases. Now about a week later Unless you actually executed 2565134864 transactions in that one week, this is still data corruption. Check for faulty memory. I'd be more inclined to agree with you if it happened on only one server machine. But this has now happened on two different machines in the space of a week. My understanding is that the transaction id logged is garbage because the bookkeeping fields have been clobbered for some tuple(s). The one last week was really low (like 1000). -Casey ---(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] postgresql 8.2 Installation error at gmake
Hello, Configure will work anyway without the --disable-spinlock; you're wasting your time with that option. Anyway, please note that your linker problem does not seem to be related to any of these options. Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. All right. I know how it caused the problem for me. My gcc was not setup correctly. After changing the gcc, it works for me now :). Just have one comments about this, (1) ./configure --prefix=/local/postgresql --without-docdir --without-readline --without-zlib CC=/usr/bin/gcc Success for both 8.2.2 8.2.3 :) (2) gmake Success for 8.2.2 :) But for base-8.2.3, I have the following error msg: gmake -C pl all gmake[2]: Entering directory `/postgresql-8.2.3/src/pl' gmake[3]: Entering directory `/postgresql-8.2.3/src/pl/plpgsql' gmake -C src all gmake[4]: Entering directory `/postgresql-8.2.3/src/pl/plpgsql/src' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/postgresql-8.2.3/src/pl/plpgsql/src' gmake[3]: Leaving directory `/postgresql-8.2.3/src/pl/plpgsql' gmake[2]: Leaving directory `/postgresql-8.2.3/src/pl' gmake -C makefiles all gmake[2]: Entering directory `/postgresql-8.2.3/src/makefiles' gmake[2]: Nothing to be done for `all'. gmake[2]: Leaving directory `/postgresql-8.2.3/src/makefiles' gmake -C test/regress all gmake: *** test/regress: No such file or directory. Stop. gmake: Entering an unknown directorygmake: Leaving an unknown directorygmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/postgresql-8.2.3/src' gmake: *** [all] Error 2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] how to hide database objects from users, that don't have access to them
Hi, Actually we've got a problem developing a DB with a lot of logic in it: Our database has couple different interfaces to other systems (made using views and procedures) and some interfaces need to be public or semi-public. Even if we grant all rights correctly, each user, that able to connect to the DB, has rights to view structures of all it components (procedures/views/tables..) even he has no kind of access rights to them (!) We've been little in trouble about, as far we can't open structure and especial plenty a lot of logic inside in procedures (mainly written in pgsql) to all users, as far we can't trust them. If there any workaround to fix this and provide different users interfaces to DB showing only objects, they have access to? really would be grateful for help, as far we notice this hidden trouble to late to change project design. --- Sincerely yours, Oleksandr Pryymak ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem writing sql statement....
I have a table that I want to find rows that have the same value in two fields, e.g. all rows that have the same date and also the same productionid... How do I write such an sql statement? Regards, BTJ -- --- Bjørn T Johansen [EMAIL PROTECTED] --- Someone wrote: I understand that if you play a Windows CD backwards you hear strange Satanic messages To which someone replied: It's even worse than that; play it forwards and it installs Windows --- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Where art thou pg_clog?
On Feb 15, 2007, at 1:46 PM, Alvaro Herrera wrote: Casey Duncan wrote: We have a production system with multiple identical database instances on the same hardware, with the same configuration, running databases with the exact same schema. They each have different data, but the database sizes and load patterns are almost exactly the same. We are running pg 8.1.5 (upgraded the day before 8.1.6 came out, oh well ;^) and since then we have noticed the following error on two of the servers: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory Can you relate it to autovacuum? Maybe. Here's what I get when I crank up the logging to debug4: 2007-02-15 14:20:48.771 PST DEBUG: StartTransaction 2007-02-15 14:20:48.771 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052708/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.771 PST DEBUG: vacuuming pg_catalog.pg_statistic 2007-02-15 14:20:48.771 PST ERROR: could not access status of transaction 2565134864 2007-02-15 14:20:48.772 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory 2007-02-15 14:20:48.772 PST DEBUG: proc_exit(0) 2007-02-15 14:20:48.772 PST DEBUG: shmem_exit(0) 2007-02-15 14:20:48.773 PST DEBUG: exit(0) 2007-02-15 14:20:48.775 PST DEBUG: reaping dead processes does that imply that it is the pg_statistic table that is hosed? Interestingly I can manually vacuum that table in all of the databases on this machine without provoking the error. -Casey ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Where art thou pg_clog?
Casey Duncan wrote: On Feb 15, 2007, at 1:46 PM, Alvaro Herrera wrote: Casey Duncan wrote: We have a production system with multiple identical database instances on the same hardware, with the same configuration, running databases with the exact same schema. They each have different data, but the database sizes and load patterns are almost exactly the same. We are running pg 8.1.5 (upgraded the day before 8.1.6 came out, oh well ;^) and since then we have noticed the following error on two of the servers: 2007-02-15 00:35:03.324 PST ERROR: could not access status of transaction 2565134864 2007-02-15 00:35:03.325 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory Can you relate it to autovacuum? Maybe. Here's what I get when I crank up the logging to debug4: 2007-02-15 14:20:48.771 PST DEBUG: StartTransaction 2007-02-15 14:20:48.771 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052708/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.771 PST DEBUG: vacuuming pg_catalog.pg_statistic 2007-02-15 14:20:48.771 PST ERROR: could not access status of transaction 2565134864 2007-02-15 14:20:48.772 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory 2007-02-15 14:20:48.772 PST DEBUG: proc_exit(0) 2007-02-15 14:20:48.772 PST DEBUG: shmem_exit(0) 2007-02-15 14:20:48.773 PST DEBUG: exit(0) 2007-02-15 14:20:48.775 PST DEBUG: reaping dead processes does that imply that it is the pg_statistic table that is hosed? Interestingly I can manually vacuum that table in all of the databases on this machine without provoking the error. Except template0 I presume? Is this autovacuum running in template0 perchance? I note that 800 million transactions have passed since the Xid in the error message was current. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem writing sql statement....
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/15/07 15:13, Bjørn T Johansen wrote: I have a table that I want to find rows that have the same value in two fields, e.g. all rows that have the same date and also the same productionid... How do I write such an sql statement? If I understand your question: SELECT FIELD_1, FIELD_2, COUNT(*) FROM A_TABLE WHERE SOME_DATE = '-mm-dd' AND PRODUCTIONID = GROUP BY FIELD_1, FIELD_2 HAVING COUNT(*) 1; -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1OK9S9HxQb37XmcRAhC9AJ9YKyb2HRhr+FAaWQluMG86lyV6egCgu0LU 3KT/s+eq5KKHSYDnpRKuyu4= =SgpW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] invalid regular expression: invalid backreference number
I've got a function that generates usernames and passwords on insert if they haven't yet been set. The code block is: -- create a new username for new people IF (LENGTH(COALESCE(new_pp_username, '')) = 0) THEN LOOP gen_pp_username := LOWER(SUBSTRING(new_pp_first_name from 1 for 2)) || LOWER(SUBSTRING(new_pp_last_name from 1 for 8)) || round(random()*100); gen_pp_username := regexp_replace(gen_pp_username, E'\\W', '', 'g'); EXIT WHEN ((SELECT COUNT(*) FROM people WHERE pp_username = gen_pp_username AND pp_provisional_p='f') = 0); END LOOP; ELSE gen_pp_username := new_pp_username; END IF; -- create a new password if there is none IF (LENGTH(COALESCE(new_pp_password, '')) = 0) THEN chars := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'; FOR i in 1..8 LOOP gen_pp_password := gen_pp_password || SUBSTRING(chars, ceil(random()*LENGTH(chars)), 1);2007-02-15 15:32:57.264727500 END LOOP; ELSE gen_pp_password := new_pp_password; END IF; This used to work before my upgrade to 8.2.1. The error the function now throws is: jross%wykidsERROR: invalid regular expression: invalid backreference number 2007-02-15 15:32:57.264729500 jross%wykidsCONTEXT: SQL function substring statement 1 2007-02-15 15:32:57.264730500 PL/pgSQL function set_people line 58 at assignment I've futzed around with the various ways I can call substring, but I don't understand why this is throwing the error. Any help would be greatly appreciated! Jeff Ross ---(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] postgresql 8.2 Installation error at gmake
Emi Lu wrote: (2) gmake Success for 8.2.2 :) But for base-8.2.3, I have the following error msg: Forget those base stuff. It's broken. Just get the whole package. It's not that big anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Keyword OWNED not recognized in pg v. 8.1
Hi, On Tue, 2007-02-13 at 08:57 +0100, dfx wrote: I tryied also with .rpm downloaded from postgres official site but I get a lot of unresolved dependencies. You should not have gotten dependency errors if you did not miss to install compat package. So I ask if it exist a document that explain step by step the process to upgrade v. 8.1 to v. 8.2 under Fedora Core 6, a document for novices, as I am. http://pgfoundry.org/docman/view.php/148/98/PostgreSQL-RPM-Installation-PGDG.pdf Let us know if you have more questions. -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
[GENERAL] ROLE INHERIT
Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called administrator (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? Thanks David
Re: [GENERAL] ROLE INHERIT
David Legault wrote: Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called administrator (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? Thanks David Doesn't the inherit property need to be on the role that will do the inheriting? I.e. if admin is to inherit the privileges of administrator, then admin needs the inherit property. A role with the INHERIT attribute can automatically use whatever database privileges have been granted to all roles it is directly or indirectly a member of. -- Paul Lambert Database Administrator AutoLedgers ---(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] ROLE INHERIT
David Legault escribió: Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called administrator (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? A single point, which is that while the privileges that are inherited are those that you can GRANT and REVOKE with the respective commands. CREATEROLE and the others are not inherited. Also, keep in mind that while role admin does not have CREATEDB privilege, if you grant it the CREATEROLE privilege it will easily be able to create a database by creating another role with CREATEDB privilege. So don't grant CREATEROLE to just anyone. FYI, the short name of PostgreSQL is Postgres, not Postgre. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ROLE INHERIT
The docs should probably be more clear about this because you can do: GRANT *role* [, ...] TO *username* [, ...] [ WITH ADMIN OPTION ] GRANT on Roles This variant of the GRANT command grants membership in a role to one or more other roles. Membership in a role is significant because it conveys the privileges granted to a role to each of its members. GRANT administrator TO admin; I thought it would transfer that CREATEROLE privilege too. Thanks David On 2/15/07, Alvaro Herrera [EMAIL PROTECTED] wrote: David Legault escribió: Hello, I'm a bit new to Postgre, and I'm experimenting with the roles stuff. I want to know why If I create a role called administrator (a group basically, no login) : CREATE ROLE administrator NOSUPERUSER INHERIT NOCREATEDB CREATEROLE; And then create a user CREATE ROLE admin LOGIN PASSWORD 'password' ON ROLE administrator; admin doesn't have the CREATEROLE privilege himself, but because he is part of a group that has it, why doesn't this fall back on him having it? When I try to use that admin user to create another role, it says insufficient privileges. Am I missing something in this role stuff ? A single point, which is that while the privileges that are inherited are those that you can GRANT and REVOKE with the respective commands. CREATEROLE and the others are not inherited. Also, keep in mind that while role admin does not have CREATEDB privilege, if you grant it the CREATEROLE privilege it will easily be able to create a database by creating another role with CREATEDB privilege. So don't grant CREATEROLE to just anyone. FYI, the short name of PostgreSQL is Postgres, not Postgre. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: [GENERAL] Option to undo last update on table.
On 2/15/07, RPK [EMAIL PROTECTED] wrote: Is there any option in PGSQL to undo last changes done on a table? Any feature similar to FlashBack Query in Oracle. The only way I could imagine that you could implement a flashback query in PostgreSQL is if you ignored everyone's advice and didn't vacuum not a good idea. Oracle uses an undo tablespace which records all of the information to rollback to an older version of row xyz, but PostgreSQL stores a new version of row xyz in the same data file and vacuum flags the old version of row xyz for deletion which may quickly get overwritten (or may be unusable for other reasons???). As for others that may be interested, the flashback feature allows you to specify essentially the version to use when executing the query. And it is typically done by specifying the SCN (which should be equivalent to the xid for PostgreSQL) OR a timestamp. -- Chad http://www.postgresqlforums.com/
Re: [GENERAL] postgresql 8.2 Installation error at gmake
Emi Lu [EMAIL PROTECTED] writes: Do you happen to have a previous installation at /local/postgresql? I think the presence of an older libpgport.a there could be causing the confusion. (1) \rm -r /local/postgresql gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wendif-labels -fno-strict-aliasing zic.o ialloc.o scheck.o localtime.o -L../../src/port -Wl,-rpath,'/local/postgresql/lib' -lpgport -lcrypt -ldl -lm -o zic zic.o(.text+0xb5f): In function `associate': zic.c: undefined reference to `pg_qsort' zic.o(.text+0x2c54): In function `writezone': zic.c: undefined reference to `pg_qsort' collect2: ld returned 1 exit status Still, I think Alvaro must be right: somehow the link is picking up an older version of libpgport. Maybe there's one in /usr/lib or /usr/local/lib? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Where art thou pg_clog?
On Feb 15, 2007, at 2:44 PM, Alvaro Herrera wrote: Casey Duncan wrote: On Feb 15, 2007, at 1:46 PM, Alvaro Herrera wrote: [..] Can you relate it to autovacuum? Maybe. Here's what I get when I crank up the logging to debug4: 2007-02-15 14:20:48.771 PST DEBUG: StartTransaction 2007-02-15 14:20:48.771 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052708/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.771 PST DEBUG: vacuuming pg_catalog.pg_statistic 2007-02-15 14:20:48.771 PST ERROR: could not access status of transaction 2565134864 2007-02-15 14:20:48.772 PST DETAIL: could not open file pg_clog/ 098E: No such file or directory 2007-02-15 14:20:48.772 PST DEBUG: proc_exit(0) 2007-02-15 14:20:48.772 PST DEBUG: shmem_exit(0) 2007-02-15 14:20:48.773 PST DEBUG: exit(0) 2007-02-15 14:20:48.775 PST DEBUG: reaping dead processes does that imply that it is the pg_statistic table that is hosed? Interestingly I can manually vacuum that table in all of the databases on this machine without provoking the error. Except template0 I presume? Is this autovacuum running in template0 perchance? I note that 800 million transactions have passed since the Xid in the error message was current. Wouldn't you know it! A little farther back up in the log file: 2007-02-15 14:20:48.480 PST LOG: autovacuum: processing database template0 2007-02-15 14:20:48.480 PST DEBUG: StartTransaction 2007-02-15 14:20:48.480 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.481 PST DEBUG: autovacuum: VACUUM FREEZE whole database 2007-02-15 14:20:48.481 PST DEBUG: CommitTransaction 2007-02-15 14:20:48.481 PST DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: fwiw, I did a cluster-wide vacuum on 1/20/2007. Not sure if that has any impact on anything, just thought I'd throw it out there. I'm curious how template0 got stomped on. Certainly nothing's been changing it. Of course it might just be some random bug so the fact it landed on a file for template0 could be completely arbitrary. Anyhow it does seem curious to me. -Casey ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ROLE INHERIT
David Legault [EMAIL PROTECTED] writes: I thought it would transfer that CREATEROLE privilege too. This is documented someplace ... ah, under CREATE ROLE: : The INHERIT attribute governs inheritance of grantable privileges (that : is, access privileges for database objects and role memberships). It : does not apply to the special role attributes set by CREATE ROLE and : ALTER ROLE. For example, being a member of a role with CREATEDB : privilege does not immediately grant the ability to create databases, : even if INHERIT is set; it would be necessary to become that role via : SET ROLE before creating a database. The main reason we did that is that SUPERUSER seemed a bit too dangerous to be an inheritable privilege. You could argue the other role attribute bits either way, but for simplicity they all act the same. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Where art thou pg_clog?
Casey Duncan wrote: Interestingly I can manually vacuum that table in all of the databases on this machine without provoking the error. Except template0 I presume? Is this autovacuum running in template0 perchance? I note that 800 million transactions have passed since the Xid in the error message was current. Wouldn't you know it! A little farther back up in the log file: 2007-02-15 14:20:48.480 PST LOG: autovacuum: processing database template0 2007-02-15 14:20:48.480 PST DEBUG: StartTransaction 2007-02-15 14:20:48.480 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.481 PST DEBUG: autovacuum: VACUUM FREEZE whole database 2007-02-15 14:20:48.481 PST DEBUG: CommitTransaction 2007-02-15 14:20:48.481 PST DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: This is a bug we fixed in 8.1.7. I suggest you update to the latest of the 8.1 series, to get that fix among others. To fix the problem, set pg_database.datallowconn=true for template0, then connect to it and do a VACUUM FREEZE. Then set datallowconn=false again. I'm curious how template0 got stomped on. Certainly nothing's been changing it. Of course it might just be some random bug so the fact it landed on a file for template0 could be completely arbitrary. The problem is that all databases are vacuumed every so many transactions, to avoid Xid wraparound problems; even non connectable databases. The problem is that a bug in autovacuum caused that vacuum operation to neglect using the FREEZE flag; this negligence makes it leave non-permanent Xids in the tables, leading to the problem you're seeing. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] ROLE INHERIT
Tom Lane wrote: David Legault [EMAIL PROTECTED] writes: I thought it would transfer that CREATEROLE privilege too. I've been dying to get 2 cents in on this. Tell me if this suggestion makes any sense. We use real database users in our systems, we don't connect in with an over-endowed user and then arbitrate security in client code. Therefore, we depend entirely upon the server's ability to enforce security. The practical advantage of this, which is huge, is that nowhere in my Postgres settings do I have to make allowance for the web user (apache) to go root with respect to Postgres. As a general rule we consider this good of course, because a remote exploit on the web server could not do anything the user could not do anyway. Except for the hole. On a public site that lets users register, we have to have way to let the web server assume the role of somebody who has createuser privelege, and that's pretty much the end of the no-root policy. If an exploit could be placed, it could simply go into that mode and create a superuser. What would be really nice is if you could limit the ability of CREATEUSER to grant roles. A nice general solution would be to allow a user with CREATEUSER privelege to only put other users into the same groups that person is in, or perhaps into a list specified by a higher-privelege user. What's chances of anything like that showing up? And, dumb question, am I mistaking the purpose of INHERIT and it already does what I'm saying? I don't think so because INHERIT does not let somebody create users out of the void. This is documented someplace ... ah, under CREATE ROLE: : The INHERIT attribute governs inheritance of grantable privileges (that : is, access privileges for database objects and role memberships). It : does not apply to the special role attributes set by CREATE ROLE and : ALTER ROLE. For example, being a member of a role with CREATEDB : privilege does not immediately grant the ability to create databases, : even if INHERIT is set; it would be necessary to become that role via : SET ROLE before creating a database. The main reason we did that is that SUPERUSER seemed a bit too dangerous to be an inheritable privilege. You could argue the other role attribute bits either way, but for simplicity they all act the same. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend begin:vcard fn:Kenneth Downs n:Downs;Kenneth adr;dom:;;347 Main Street;East Setauket;NY;11733 email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE url:http://www.secdat.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to use slash commands in a function
Hi guys, I was wondering how could i (if at all possible) to use say a command like: \! touch fred.txt in a function? i.e. CREATE FUNCTION myfunc() RETURNS TRIGGER AS 'BEGIN \! touch fred.txt RETURN NEW; END;' LANGUAGE 'plpgsql'; At the moment i get: ERROR: syntax error at or near \ Does this mean i should encapsulate the line in quotation marks or something like that? Cheers. Vanessa -- View this message in context: http://www.nabble.com/How-to-use-slash-commands-in-a-function-tf3237240.html#a8997475 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Where art thou pg_clog?
On Feb 15, 2007, at 5:21 PM, Alvaro Herrera wrote: Casey Duncan wrote: Interestingly I can manually vacuum that table in all of the databases on this machine without provoking the error. Except template0 I presume? Is this autovacuum running in template0 perchance? I note that 800 million transactions have passed since the Xid in the error message was current. Wouldn't you know it! A little farther back up in the log file: 2007-02-15 14:20:48.480 PST LOG: autovacuum: processing database template0 2007-02-15 14:20:48.480 PST DEBUG: StartTransaction 2007-02-15 14:20:48.480 PST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: 2007-02-15 14:20:48.481 PST DEBUG: autovacuum: VACUUM FREEZE whole database 2007-02-15 14:20:48.481 PST DEBUG: CommitTransaction 2007-02-15 14:20:48.481 PST DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 3429052629/1/0, nestlvl: 1, children: This is a bug we fixed in 8.1.7. I suggest you update to the latest of the 8.1 series, to get that fix among others. ok, great. To fix the problem, set pg_database.datallowconn=true for template0, then connect to it and do a VACUUM FREEZE. Then set datallowconn=false again. Do you mean to do this after upgrading to 8.1.8? If I try than in 8.1.5, I get (unsurprisingly): % psql -U postgres template0 -c vacuum freeze ERROR: could not access status of transaction 2565134864 DETAIL: could not open file pg_clog/098E: No such file or directory I'm curious how template0 got stomped on. Certainly nothing's been changing it. Of course it might just be some random bug so the fact it landed on a file for template0 could be completely arbitrary. The problem is that all databases are vacuumed every so many transactions, to avoid Xid wraparound problems; even non connectable databases. The problem is that a bug in autovacuum caused that vacuum operation to neglect using the FREEZE flag; this negligence makes it leave non-permanent Xids in the tables, leading to the problem you're seeing. Ironically we were earlier bitten by the bug that autovacuum didn't do the cluster-wide vacuum until too late. Now we got bitten by the fact that did do the cluster-wide vacuum. Talk about damned-if-you-do- and-damned-if-you-don't! 8^) ok, this is a much better sounding explanation than random data corruption ;^) Thanks! -Casey ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Where art thou pg_clog?
Casey Duncan wrote: On Feb 15, 2007, at 5:21 PM, Alvaro Herrera wrote: Casey Duncan wrote: To fix the problem, set pg_database.datallowconn=true for template0, then connect to it and do a VACUUM FREEZE. Then set datallowconn=false again. Do you mean to do this after upgrading to 8.1.8? If I try than in 8.1.5, I get (unsurprisingly): % psql -U postgres template0 -c vacuum freeze ERROR: could not access status of transaction 2565134864 DETAIL: could not open file pg_clog/098E: No such file or directory Hum, yeah, I forgot to mention that you need to create the 098E pg_clog segment for that to work at all :-) Fill it with byte 0x55 till the needed position, which is the bit pattern for all transactions committed. I'd make sure to remove it manually after the freeze is done, just in case! (I think the system would remove it at next checkpoint, but anyway.) You can do it either after or before upgrading; it's the same. The only thing that changes in 8.1.7 is that an upcoming vacuum would not forget the FREEZE. I'm curious how template0 got stomped on. Certainly nothing's been changing it. Of course it might just be some random bug so the fact it landed on a file for template0 could be completely arbitrary. The problem is that all databases are vacuumed every so many transactions, to avoid Xid wraparound problems; even non connectable databases. The problem is that a bug in autovacuum caused that vacuum operation to neglect using the FREEZE flag; this negligence makes it leave non-permanent Xids in the tables, leading to the problem you're seeing. Ironically we were earlier bitten by the bug that autovacuum didn't do the cluster-wide vacuum until too late. Now we got bitten by the fact that did do the cluster-wide vacuum. Talk about damned-if-you-do- and-damned-if-you-don't! 8^) Heh :-) Sorry, they are all my bugs. I guess you should be throwing stones at me or something. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to use slash commands in a function
vanessa escribió: Hi guys, I was wondering how could i (if at all possible) to use say a command like: \! touch fred.txt in a function? i.e. CREATE FUNCTION myfunc() RETURNS TRIGGER AS 'BEGIN \! touch fred.txt RETURN NEW; END;' LANGUAGE 'plpgsql'; At the moment i get: ERROR: syntax error at or near \ Does this mean i should encapsulate the line in quotation marks or something like that? No, it means you can't do it at all, because backslash commands are psql-only, thus you cannot put them in functions (which are server-executed). PL/pgSQL functions are trusted, meaning you can't access the outside world (disk, network, etc) with them. If you really need to do that, consider using an untrusted language (C, plperlu, etc). I think there's even a PL/sh (shell) but I don't think you can use it for trigger functions. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Inequality operators are not deduced.
Hello, I found that the planner can decude equality operators, but cannot decude inequality ones. Are there any plans to improve handling of them? I initialized tables as below. CREATE TABLE T (i INTEGER PRIMARY KEY); CREATE TABLE U (i INTEGER PRIMARY KEY); INSERT INTO T SELECT generate_series(1, 1); INSERT INTO U SELECT generate_series(1, 10); ANALYZE; The planner can add an implicit equality operator, so the folloing two plans are exactly the same, regardless of the redundant 'U.i = 100'. EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i = 100; EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i = 100 AND U.i = 100; QUERY PLAN -- Nested Loop (cost=0.00..16.56 rows=1 width=8) - Index Scan using t_pkey on t (cost=0.00..8.27 rows=1 width=4) Index Cond: (i = 100) - Index Scan using u_pkey on u (cost=0.00..8.28 rows=1 width=4) Index Cond: (i = 100) However, it seems to be inapplicable for inequality operators. The plan was improved after I added the deduce-able 'U.i = 100' in theory. EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100; QUERY PLAN - Merge Join (cost=0.00..340.38 rows=100 width=8) Merge Cond: (t.i = u.i) - Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4) Index Cond: (i 100) - Index Scan using u_pkey on u (cost=0.00..3048.26 rows=10 width=4) EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100 AND U.i 100; QUERY PLAN - Merge Join (cost=0.00..11.32 rows=1 width=8) Merge Cond: (t.i = u.i) - Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4) Index Cond: (i 100) - Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4) Index Cond: (i 100) Just for the record, if forcing nested loop joins, plans were the follows. SET enable_mergejoin = off; SET enable_hashjoin = off; EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100; QUERY PLAN - Nested Loop (cost=0.00..739.11 rows=100 width=8) - Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4) Index Cond: (i 100) - Index Scan using u_pkey on u (cost=0.00..7.28 rows=1 width=4) Index Cond: (u.i = t.i) EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100 AND U.i 100; QUERY PLAN --- Nested Loop (cost=0.00..337.42 rows=1 width=8) - Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4) Index Cond: (i 100) - Index Scan using t_pkey on t (cost=0.00..3.40 rows=1 width=4) Index Cond: ((t.i 100) AND (t.i = u.i)) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Small request re error message
Could the below message be modified: The database cluster was initialized with PG_CONTROL_VERSION 906166272, but the server was compiled with PG_CONTROL_VERSION 822. By also showing the version numbers in hex, like so: The database cluster was initialized with PG_CONTROL_VERSION 906166272 (0x3603), but the server was compiled with PG_CONTROL_VERSION 822 (0x0336). This would have saved me a couple of minutes' worth of puzzlement. With Macs out there that look identical between the PPC and x86 versions, this kind of error has gotten a lot easier to make ;-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How to append the contents of a table to a file
Hello! Does anyone have any idea about how to append the contents of a table to a file? Thanks. Vanessa -- View this message in context: http://www.nabble.com/How-to-append-the-contents-of-a-table-to-a-file-tf3237484.html#a8998198 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Inequality operators are not deduced.
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I found that the planner can decude equality operators, but cannot decude inequality ones. Are there any plans to improve handling of them? Not particularly; it doesn't seem like something that comes up often enough to be worth the work. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Inequality operators are not deduced.
I wrote: However, it seems to be inapplicable for inequality operators. The plan was improved after I added the deduce-able 'U.i = 100' in theory. Sorry, there was a miss. The correct is 'U.i 100'. EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100; QUERY PLAN - Merge Join (cost=0.00..340.38 rows=100 width=8) Merge Cond: (t.i = u.i) - Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4) Index Cond: (i 100) - Index Scan using u_pkey on u (cost=0.00..3048.26 rows=10 width=4) EXPLAIN SELECT * FROM T, U WHERE T.i = U.i AND T.i 100 AND U.i 100; QUERY PLAN - Merge Join (cost=0.00..11.32 rows=1 width=8) Merge Cond: (t.i = u.i) - Index Scan using t_pkey on t (cost=0.00..10.00 rows=100 width=4) Index Cond: (i 100) - Index Scan using u_pkey on u (cost=0.00..9.94 rows=96 width=4) Index Cond: (i 100) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to append the contents of a table to a file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/15/07 20:59, vanessa wrote: Hello! Does anyone have any idea about how to append the contents of a table to a file? psql will do the what you asked in this question, but you are probably needing more than you ask. If so, C, Python Perl spring quickly to mind. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF1R/AS9HxQb37XmcRAjS/AJ0fPphMP/wvdJLygvV/+lJcH130XwCgnVJp d6A7/Gn2QV8HMxKvkKpw0NQ= =jPKm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
