Re: [GENERAL] Can I use a query with UPDATE on its SET?
Eus wrote: > Hi Ho! > > Since I can do: > > INSERT INTO table (SELECT a_transaction.*); > > I am wondering whether I can do: > > UPDATE table SET (SELECT a_transaction.*) WHERE primary_key = (SELECT > a_transaction.primary_key); UPDATE table SET fieldname = (SELECT ..) WHERE primary_key = (SELECT...) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foxpro, odbc, data types and unnecessary convertions
Fernando Moreno writes: > For numbers, I have to convert them first to string and then remove > the spaces, the code looks like this: sql_string = "some sql" + > alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and > str in a third function but it's still tricky. A shorter and > presumably better way to do the same is: sql_string = "some_column = > ?foxpro_variable ". The problem with the last option is that, watching > the pgsql log, values are sent this way: '12345'::float(8), so for > every numeric value, no matter its type, I'm sending 12 characters > more and the server is doing convertions that I don't need. > Having a lot of foreign keys and other numeric data, I think this > behaviour is not so good for network (remote and poor connection) and > server performance. I'm almost decided to keep doing the trim/str > thing, but my question is: am I exaggerating? what would you do? You're obsessing over an issue that is almost certainly not going to make a measurable difference. You can probably improve your application performance a lot more by expending the same effort somewhere else. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Tangent Ref: [GENERAL] Valid characters for user/role/group names?
Roderick A. Anderson wrote: In my continuing quest for multi-tenant ways I'm trying to come up with a method to name roles, users, and groups that will not clash across the cluster. While testing and researching I discovered prior work. I was using different terminology. http://wiki.postgresql.org/wiki/Shared_Database_Hosting Well my need go a little deeper but they were touched on. There is also the thread from last August. http://archives.postgresql.org/pgsql-admin/2008-08/msg00049.php My thanks to Sam Mason and Tom Lane for the ideas and suggestions. I'm still working through where quoting is needed and not when using pgAdmin III and psql. Rod -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
On Wed, 2009-02-25 at 22:06 +, Thom Brown wrote: > > Looks like you didn't run cmd_archiver -C -I > > > > Ahh, okay, that did something, which I think means it created a > directory named after the slave IP in the archive directory. Right that is the queue directory. > Now when I run "./cmd_archiver -C cmd_archiver.ini" I get: > That command really shouldn't do anything but error. The whole point of the archiver is to be placed in the archive_command option in the postgresql.conf. E.g; cmd_archive -C cmd_archive.ini -F %p This really should be happening on the pitrtools list. Let's bounce over there and resolve this. Joshua D. Drake > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
> Looks like you didn't run cmd_archiver -C -I > Ahh, okay, that did something, which I think means it created a directory named after the slave IP in the archive directory. I didn't see any mention of that switch in the README file, unless it's mentioned elsewhere and I missed it. Now when I run "./cmd_archiver -C cmd_archiver.ini" I get: rsync: link_stat "/var/lib/postgresql/pitr_tools/None" failed: No such file or directory (2) rsync error: some files could not be transferred (code 23) at main.c(1058) [sender=3.0.3] rsync: link_stat "/var/lib/postgresql/pitr_tools/None" failed: No such file or directory (2) rsync error: some files could not be transferred (code 23) at main.c(1058) [sender=3.0.3] FATAL: Unabled to rsync_transfer or queue_transfer CRITICAL 5888
Re: [GENERAL] Warm standby failover mechanism
On Wed, 2009-02-25 at 21:39 +, Thom Brown wrote: > > As a note, all PITRTools does is wrap around all the tools > that you are > trying to make work. So you will still need pg_standby, rsync, > ssh > etc... > > I have updated the wiki to make it a bit more friendly. > > https://projects.commandprompt.com/public/pitrtools/wiki > > > > I've given PITRTools a try, but I have been unsuccessful in utilising > it. I've configured both ini files, everything owned by user > postgres, and upon running "./cmd_archiver -C cmd_archiver.ini" I get: > > NOTICE: check_config_func() > NOTICE: Performing standard archive > NOTICE: archive_func() > NOTICE: send_queue_func() > NOTICE: list_queue_func() > NOTICE: generate_slave_list_func() > NOTICE: Your slaves are: ['192.168.1.17'] > Traceback (most recent call last): > File "../pitr_tools/cmd_archiver", line 343, in > archive_func() > File "../pitr_tools/cmd_archiver", line 254, in archive_func > queue = send_queue_func() > File "../pitr_tools/cmd_archiver", line 219, in send_queue_func > for host in list_queue_func(): > File "../pitr_tools/cmd_archiver", line 202, in list_queue_func > list_archives = os.listdir(queuedir) > OSError: [Errno 2] No such file or directory: > '/var/lib/postgresql/archive/192.168.1.17' > > The only time that IP address appears in the INI file is on the slaves > line, so I'm not sure why it's trying to find a directory called that. Looks like you didn't run cmd_archiver -C -I Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] foxpro, odbc, data types and unnecessary convertions
Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = "some sql" + alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = "some_column = ?foxpro_variable ". The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
> As a note, all PITRTools does is wrap around all the tools that you are > trying to make work. So you will still need pg_standby, rsync, ssh > etc... > > I have updated the wiki to make it a bit more friendly. > > https://projects.commandprompt.com/public/pitrtools/wiki > > I've given PITRTools a try, but I have been unsuccessful in utilising it. I've configured both ini files, everything owned by user postgres, and upon running "./cmd_archiver -C cmd_archiver.ini" I get: NOTICE: check_config_func() NOTICE: Performing standard archive NOTICE: archive_func() NOTICE: send_queue_func() NOTICE: list_queue_func() NOTICE: generate_slave_list_func() NOTICE: Your slaves are: ['192.168.1.17'] Traceback (most recent call last): File "../pitr_tools/cmd_archiver", line 343, in archive_func() File "../pitr_tools/cmd_archiver", line 254, in archive_func queue = send_queue_func() File "../pitr_tools/cmd_archiver", line 219, in send_queue_func for host in list_queue_func(): File "../pitr_tools/cmd_archiver", line 202, in list_queue_func list_archives = os.listdir(queuedir) OSError: [Errno 2] No such file or directory: '/var/lib/postgresql/archive/ 192.168.1.17' The only time that IP address appears in the INI file is on the slaves line, so I'm not sure why it's trying to find a directory called that.
Re: [GENERAL] Postgres SRPMs for RHEL
Devrim GÜNDÜZ wrote: On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ 'cause I was a bit lazy to sync srpms from main repository. It is my bad, and will start pushing packages later today. If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. Please take a look at here: http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html Using pgsqlrpms site, you can download SRPMs using yum: yumdownloader --enablerepo pgdg81-source --source postgresql -HTH. That'll work! Thanks. -- Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres SRPMs for RHEL
On Wed, 2009-02-25 at 12:19 -0600, Justin Pasher wrote: > >> If I cycle through the versions, the last version in the 8.1 branch I > >> can find with source RPMs is 8.1.14. > >> > > > > http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ > > > > Unless I'm just looking for the wrong filename, I still can't fine the > source RPMs on the yum repo either, just the regular RPMs. Doh! Yep you are right. Sorry for the noise. Joshua D. Drake > > > -- > Justin Pasher > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres SRPMs for RHEL
On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: > Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't > show > up here? > > http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ 'cause I was a bit lazy to sync srpms from main repository. It is my bad, and will start pushing packages later today. > > If I cycle through the versions, the last version in the 8.1 branch I > can find with source RPMs is 8.1.14. Please take a look at here: http://yum.pgsqlrpms.org/srpms/8.1/redhat/rhel-4-i386/repoview/postgresql.html Using pgsqlrpms site, you can download SRPMs using yum: yumdownloader --enablerepo pgdg81-source --source postgresql -HTH. -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Postgres SRPMs for RHEL
Joshua D. Drake wrote: On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ Unless I'm just looking for the wrong filename, I still can't fine the source RPMs on the yum repo either, just the regular RPMs. -- Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres SRPMs for RHEL
On Wed, 2009-02-25 at 12:10 -0600, Justin Pasher wrote: > Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show > up here? > > http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ > > If I cycle through the versions, the last version in the 8.1 branch I > can find with source RPMs is 8.1.14. http://yum.pgsqlrpms.org/8.1/redhat/rhel-4ES-i386/ > > > -- > Justin Pasher > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres SRPMs for RHEL
Is there a reason why the source RPMs for PG 8.1.16 on RHEL don't show up here? http://www.postgresql.org/ftp/binary/v8.1.16/linux/srpms/redhat/rhel-4-i386/ If I cycle through the versions, the last version in the 8.1 branch I can find with source RPMs is 8.1.14. -- Justin Pasher -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
On Wed, 2009-02-25 at 16:33 +, Thom Brown wrote: > You are doing this the hard way. Grab PITRTTools. > > https://projects.commandprompt.com/public/pitrtools > > > I can't really dispute a recommendation from JD. I'll have to look > into that. It's a shame because we've spent ages trying to work out > where we've been going wrong in this whole process (we still haven't > got it picking up WALs from the archive). As a note, all PITRTools does is wrap around all the tools that you are trying to make work. So you will still need pg_standby, rsync, ssh etc... I have updated the wiki to make it a bit more friendly. https://projects.commandprompt.com/public/pitrtools/wiki Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Pavel Stehule schrieb: > Hello > > when you would to change name of table - you have to use dynamic query > > 38.7.2.2. OPEN FOR EXECUTE > > OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string; > > > http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html > > regards > Pavel Stehule Hi Pavel, thanks a lot. It works for me like this: CREATE OR REPLACE FUNCTION user_cursor_test(refcursor,text) RETURNS refcursor AS $$ BEGIN OPEN $1 FOR EXECUTE 'SELECT name FROM ' || $2; RETURN $1; END; $$ LANGUAGE plpgsql; Cheers Andy > 2009/2/25 Andreas Wenk : > Hi, > > short question. Why is this not possible: > > CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$ > DECLARE >curs1 CURSOR FOR SELECT * FROM $1; > BEGIN >OPEN curs1; >RETURN curs1; > END; $$ LANGUAGE plpgsql; > > Or, how do I set a parameter when using cursor? > > Thanks in advance > >> - -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general >> -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpYU2Va7znmSP9AwRAgGAAJ9En2TEupu7t994DIxi9ql5LH5sXACdHIi1 jNiaLX2mEmYWRxHDcGavbsM= =XUcF -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Several simultaneous libpq connections from the same application to different servers using different SSL certs
Knut P. Lehre wrote: > When using a libpq-based clientlib with SSL, libpq gets crt and key > files from %APPDATA%\postgresql (on MS Windows) (from now on called > "DIR"). I'd like to connect from the same app to two different pg > servers using a different set of crt/key files. One way of doing that > could have been to first connect to one server, then replace the files > in DIR, and connect to the other server. However, that does not work. It > seems the app still uses the info from the files which were in DIR > during the first connection. 1) Is there a way to specify which DIR to > use for a particular connection? 2) If not, will this be available in pg > 8.4? 3) Is there a way to merge different crt/key file sets in the same > DIR to allow libpq using different ones for different connections? 4) > Other suggestions of how to solve my problem using pg 8.3? No, this is not possible with 8.3. You can use different keys, but not different certificates. 8.4 will give you the ability to specify certificates on a per-connection basis. //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Valid characters for user/role/group names?
Sam Mason writes: > You'd want to put double quotes around the identifier. Try searching > for "quoted identifier" in the above page. Note that the double quotes would be needed when referencing the role identifier in SQL commands (eg CREATE ROLE). In other contexts, such as pg_hba.conf or psql's command-line -U switch, the rules might well be different; you might not need quotes, or you might need some other kind of quoting. I'd suggest a bit of experimenting before you settle on a grand plan. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Valid characters for user/role/group names?
On Wed, Feb 25, 2009 at 08:50:15AM -0800, Roderick A. Anderson wrote: > http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html > > "SQL identifiers and key words must begin with a letter (a-z, but also > letters with diacritical marks and non-Latin letters) or an underscore > (_). Subsequent characters in an identifier or key word can be letters, > underscores, digits (0-9), or dollar signs ($). Note that dollar signs > are not allowed in identifiers according to the letter of the SQL > standard, so their use might render applications less portable. ... " You'd want to put double quotes around the identifier. Try searching for "quoted identifier" in the above page. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Valid characters for user/role/group names?
In my continuing quest for multi-tenant ways I'm trying to come up with a method to name roles, users, and groups that will not clash across the cluster. The plans are to have one database per tenant and place applications in different schema in those databases. This is working fine so far but I'm now trying to come up with a naming scheme that will allow a dba account for tenant_1, tenant_2, etc. I found, several months ago, a posting about using the at "@" symbol in a role name so there could be a d...@tenant_1, d...@tenant_2, etc. I can't find the article again but I remember there was a reference to possible issues with using the '@' and username entries in pg_hba.conf If the '@' isn't a problem (other than possibly not being valid according to the SQL standard I was going to use an '@tenant_1.txt' entry in pg_hba.conf and place d...@tenant_1, b...@tenant_1, sa...@tenant_1 in it. But according to http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html "SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($). Note that dollar signs are not allowed in identifiers according to the letter of the SQL standard, so their use might render applications less portable. ... " I'm wondering what the reference to "non-Latin" letters means. Anyone have a solution to this? Thanks, Rod -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
> > You are doing this the hard way. Grab PITRTTools. > > https://projects.commandprompt.com/public/pitrtools > > > I can't really dispute a recommendation from JD. I'll have to look into that. It's a shame because we've spent ages trying to work out where we've been going wrong in this whole process (we still haven't got it picking up WALs from the archive). Thanks! Thom
[GENERAL] Several simultaneous libpq connections from the same application to different servers using different SSL certs
When using a libpq-based clientlib with SSL, libpq gets crt and key files from %APPDATA%\postgresql (on MS Windows) (from now on called "DIR"). I'd like to connect from the same app to two different pg servers using a different set of crt/key files. One way of doing that could have been to first connect to one server, then replace the files in DIR, and connect to the other server. However, that does not work. It seems the app still uses the info from the files which were in DIR during the first connection. 1) Is there a way to specify which DIR to use for a particular connection? 2) If not, will this be available in pg 8.4? 3) Is there a way to merge different crt/key file sets in the same DIR to allow libpq using different ones for different connections? 4) Other suggestions of how to solve my problem using pg 8.3?
Re: [GENERAL] Using xmin to identify last modified rows
Richard Broersma writes: > On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark wrote: >> I'm not sure using xmin is such a great idea really. It's handy for ad-hoc >> queries but there are all kinds of cases where it might not give you the >> results you expect. > Its been a while since the following emails were written. Has the > treatment of xmin changed since then, or is using a timestamp a better > practice? > http://archives.postgresql.org/pgsql-novice/2007-02/msg00079.php > http://archives.postgresql.org/pgsql-hackers/2004-02/msg00654.php Those statements are all still true, but notice that nowhere do they suggest doing anything except simple equality comparisons on XIDs. The OP was looking for ordering, which is a lot trickier, especially if you might be dealing with old (frozen) tuples. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cursor question
Hello when you would to change name of table - you have to use dynamic query 38.7.2.2. OPEN FOR EXECUTE OPEN unbound_cursor [ [ NO ] SCROLL ] FOR EXECUTE query_string; http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html regards Pavel Stehule 2009/2/25 Andreas Wenk : > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Hi, > > short question. Why is this not possible: > > CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$ > DECLARE > curs1 CURSOR FOR SELECT * FROM $1; > BEGIN > OPEN curs1; > RETURN curs1; > END; $$ LANGUAGE plpgsql; > > Or, how do I set a parameter when using cursor? > > Thanks in advance > > - -- > St.Pauli - Hamburg - Germany > > Andreas Wenk > > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.6 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFJpWlyVa7znmSP9AwRAv2MAJ9jHICtLeIEv+QVzeqBWSkheXDmpgCgwMUH > t0/HWRPsx56jGhSVUQXyb9I= > =XMXi > -END PGP SIGNATURE- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
On Wed, 2009-02-25 at 13:54 +, Thom Brown wrote: > I'm still trying to work out how this file creation/deletion thing > will work. If I can tag a "&& touch /tmp/pg.trigger" command to the > end of the recovery command, how often will that be called? If I > can't, I still need to ensure that it is created and deleted before > the recovery command is called, otherwise it will see it before it is > deleted and put itself online. > > Has anyone got a practical example of what they've set up, or know > what others have set up? You are doing this the hard way. Grab PITRTTools. https://projects.commandprompt.com/public/pitrtools Just pull down the stable branch: svn co https://projects.commandprompt.com/public/pitrtools/repo/branches/1.2 Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] cursor question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, short question. Why is this not possible: CREATE OR REPLACE FUNCTION user_cursor_open(text) RETURNS refcursor AS $$ DECLARE curs1 CURSOR FOR SELECT * FROM $1; BEGIN OPEN curs1; RETURN curs1; END; $$ LANGUAGE plpgsql; Or, how do I set a parameter when using cursor? Thanks in advance - -- St.Pauli - Hamburg - Germany Andreas Wenk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpWlyVa7znmSP9AwRAv2MAJ9jHICtLeIEv+QVzeqBWSkheXDmpgCgwMUH t0/HWRPsx56jGhSVUQXyb9I= =XMXi -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin to identify last modified rows
Richard Broersma writes: > On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark wrote: > >> I'm not sure using xmin is such a great idea really. It's handy for ad-hoc >> queries but there are all kinds of cases where it might not give you the >> results you expect. > > > Its been a while since the following emails were written. Has the > treatment of xmin changed since then, or is using a timestamp a better > practice? > > http://archives.postgresql.org/pgsql-novice/2007-02/msg00079.php > http://archives.postgresql.org/pgsql-hackers/2004-02/msg00654.php Well those emails aren't discussing evaluating when records were updated or deciding which were updated more recently than others. All they say is that in Postgres if ctid and xmin both match then you're looking at the same version of the same record. For a gui table editor or ODBC driver that's an important thing to know. If what you want to do is find records which have been updated for something like a "Recently updated pages" it's unlikely that the desired behaviour will exactly match how Postgres works. You're better off deciding the policy you want and writing code to implement that. Some examples of how xmin might not do what you expect: The order in which transactions *start* will determine the ordering, not the order in which they commit. If you look at records you've updated in the same transaction it's even possible to see records which come from the "future". If any records have frozen you lose any record of what order they were created. Another example is that it's impossible to ignore "trivial" updates -- any update will update xmin no matter how trivial, even if no columns are updated. Worse, in the future there may be changes to database internals which change when xmin is updated which won't match your desired policy. For example if we decide to replace VACUUM FULL with something which does no-op updates instead of moving tuples then you'll find records spontaneously appearing to have been recently updated. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin to identify last modified rows
Stéphane A. Schildknecht wrote: > Trying to identify last modified (updated or inserted) rows in a table, I > thought I could use xmin. > > I tried is to get some lines sorted by xmin. > > When doing it on a slonified database, I had no problem getting these lines. > > But, trying the same query on a non slonified DB, I got an error, as there is > no ordering operator for xid. > > I think that in the slon case, the query uses the implicit cast xid->xxid, and > then the operator to sort xxid. > > What would be the best way to get last modified rows? > > What I tried : > db=# select id_table, date_table, code_table from tb_table order by xmin desc > limit 10; > ERROR: could not identify an ordering operator for type xid > ASTUCE : Use an explicit ordering operator or modify the query. What about: test=> SELECT xmin, * FROM mausi ORDER BY xmin::text::bigint; xmin | id | val ---++ 14005 | 1 | test\test/test 14040 | 3 | mamma (2 rows) Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore DB
Shahbaz A. Tyagi wrote: We took using PgAdminIII right click action. And it generated .backup files. However while restoring whole machine is getting hanged for unlimited time. So just checking do we have some other way also, as we have complete Postgre directory also. Thanks, Shahbaz A. Tyagi Sphere Networks -Original Message- From: Ashish Karalkar [mailto:ashis...@synechron.com] Sent: Wednesday, February 25, 2009 4:38 PM To: Shahbaz A. Tyagi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Restore DB Shahbaz A. Tyagi wrote: Hi, We are using Postgres 8.3. We have backed up our db and while trying to restore, its not able to. We are using PgAdminIII for the same. What all other ways we have to restore the db. We have both the old data directory as well as backed up data. Shabz What is the Error? There are many ways to restore a backup and depends on how you took the backup. Plz lets us know how u took the backup and what O/S you r using --Ashish I am considering that you have taken this directory backup when server was shutdown. Place the directory at desired location and start the server as a postgres user pg_ctl -D start -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin to identify last modified rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Gregory Stark a écrit : > "Stéphane A. Schildknecht" writes: (...) > > You could order by age(xmin) instead > >> What would be the best way to get last modified rows? > > I'm not sure using xmin is such a great idea really. It's handy for ad-hoc > queries but there are all kinds of cases where it might not give you the > results you expect. > > You probably want to put a timestamp column on your tables and manage the date > you put in their according to a policy you control. > > Gregory, Thanks for the answer. A timestamp would surely be a better idea. BTW, I don't have hand on the schema yet and was just looking for a quick way to get some last modified rows. Regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpV6qA+REPKWGI0ERAhq/AJwNt845SDujYmFhe4aTqI30QBBC9gCg4vcH edlSZti3KDtozJ82Od0nErQ= =z1lm -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
I'm still trying to work out how this file creation/deletion thing will work. If I can tag a "&& touch /tmp/pg.trigger" command to the end of the recovery command, how often will that be called? If I can't, I still need to ensure that it is created and deleted before the recovery command is called, otherwise it will see it before it is deleted and put itself online. Has anyone got a practical example of what they've set up, or know what others have set up?
Re: [GENERAL] Restore DB
We took using PgAdminIII right click action. And it generated .backup files. However while restoring whole machine is getting hanged for unlimited time. So just checking do we have some other way also, as we have complete Postgre directory also. Thanks, Shahbaz A. Tyagi Sphere Networks -Original Message- From: Ashish Karalkar [mailto:ashis...@synechron.com] Sent: Wednesday, February 25, 2009 4:38 PM To: Shahbaz A. Tyagi Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Restore DB Shahbaz A. Tyagi wrote: > > Hi, > > > > We are using Postgres 8.3. We have backed up our db and while trying > to restore, its not able to. We are using PgAdminIII for the same. > > > > What all other ways we have to restore the db. We have both the old > data directory as well as backed up data. > > > > > > Shabz > What is the Error? There are many ways to restore a backup and depends on how you took the backup. Plz lets us know how u took the backup and what O/S you r using --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin to identify last modified rows
On Wed, Feb 25, 2009 at 5:21 AM, Gregory Stark wrote: > I'm not sure using xmin is such a great idea really. It's handy for ad-hoc > queries but there are all kinds of cases where it might not give you the > results you expect. Its been a while since the following emails were written. Has the treatment of xmin changed since then, or is using a timestamp a better practice? http://archives.postgresql.org/pgsql-novice/2007-02/msg00079.php http://archives.postgresql.org/pgsql-hackers/2004-02/msg00654.php -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using xmin to identify last modified rows
"Stéphane A. Schildknecht" writes: > But, trying the same query on a non slonified DB, I got an error, as there is > no ordering operator for xid. > > I think that in the slon case, the query uses the implicit cast xid->xxid, and > then the operator to sort xxid. You could order by age(xmin) instead > What would be the best way to get last modified rows? I'm not sure using xmin is such a great idea really. It's handy for ad-hoc queries but there are all kinds of cases where it might not give you the results you expect. You probably want to put a timestamp column on your tables and manage the date you put in their according to a policy you control. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] funny view/temp table problem with query
all explains: Query without view: QUERY PLAN Aggregate (cost=94419553.37..94419553.38 rows=1 width=16) -> Sort (cost=94269553.37..94294553.37 rows=1000 width=12) Sort Key: ss.id, (((subplan))[i.i]) -> Nested Loop (cost=93414.56..92953067.54 rows=1000 width=12) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=4) -> Materialize (cost=93414.56..93514.56 rows=1 width=8) -> Subquery Scan ss (cost=93279.56..93404.56 rows=1 width=8) -> Limit (cost=93279.56..93304.56 rows=1 width=8) -> Sort (cost=93279.56..95779.56 rows=100 width=8) Sort Key: (random()) -> Seq Scan on accounts (cost=0.00..21841.00 rows=100 width=8) SubPlan -> Limit (cost=9.25..9.27 rows=5 width=8) -> Sort (cost=9.25..9.50 rows=100 width=8) Sort Key: (random()) -> Result (cost=0.00..7.59 rows=100 width=8) One-Time Filter: ($0 > (-1)) -> Seq Scan on packages (cost=0.00..7.34 rows=100 width=8) Filter: ((id >= 1) AND (id <= 100)) (19 rows) With view used (notice, it is a bit different plan!) QUERY PLAN -- Aggregate (cost=2361251.70..2361260.98 rows=1 width=12) -> Nested Loop (cost=111239.20..2111251.70 rows=1 width=12) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=4) -> Materialize (cost=111239.20..112239.20 rows=10 width=8) -> Subquery Scan ss (cost=109889.20..39.20 rows=10 width=8) -> Limit (cost=109889.20..110139.20 rows=10 width=8) -> Sort (cost=109889.20..112389.20 rows=100 width=8) Sort Key: (random()) -> Seq Scan on accounts (cost=0.00..21841.00 rows=100 width=8) SubPlan -> Limit (cost=9.25..9.27 rows=5 width=8) -> Sort (cost=9.25..9.50 rows=100 width=8) Sort Key: (random()) -> Result (cost=0.00..7.59 rows=100 width=8) One-Time Filter: ($0 > (-1)) -> Seq Scan on packages (cost=0.00..7.34 rows=100 width=8) Filter: ((id >= 1) AND (id <= 100)) (17 rows) Create temp table based on view: explain create temp table fooheh as select * from heh; QUERY PLAN Subquery Scan ss (cost=109889.20..1037735.61 rows=10 width=8) -> Limit (cost=109889.20..110139.20 rows=10 width=8) -> Sort (cost=109889.20..112389.20 rows=100 width=8) Sort Key: (random()) -> Seq Scan on accounts (cost=0.00..21841.00 rows=100 width=8) SubPlan -> Limit (cost=9.25..9.27 rows=5 width=8) -> Sort (cost=9.25..9.50 rows=100 width=8) Sort Key: (random()) -> Result (cost=0.00..7.59 rows=100 width=8) One-Time Filter: ($0 > (-1)) -> Seq Scan on packages (cost=0.00..7.34 rows=100 width=8) Filter: ((id >= 1) AND (id <= 100)) (13 rows) and run simple query against temp table: explain select count( distinct (id, v[i])) from fooheh, generate_series(1, 5) i; QUERY PLAN -- Aggregate (cost=3226173.36..3226173.37 rows=1 width=44) -> Nested Loop (cost=2810.86..2868023.36 rows=14326 width=44) -> Function Scan on generate_series i (cost=0.00..12.50 rows=1000 width=4) -> Materialize (cost=2810.86..4243.46 rows=143260 width=40) -> Seq Scan on fooheh (cost=0.00..2667.60 rows=143260 width=40) (5 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore DB
Shahbaz A. Tyagi wrote: Hi, We are using Postgres 8.3. We have backed up our db and while trying to restore, its not able to. We are using PgAdminIII for the same. What all other ways we have to restore the db. We have both the old data directory as well as backed up data. Shabz What is the Error? There are many ways to restore a backup and depends on how you took the backup. Plz lets us know how u took the backup and what O/S you r using --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore DB
Shahbaz A. Tyagi wrote: Hi, We are using Postgres 8.3. We have backed up our db and while trying to restore, its not able to. We are using PgAdminIII for the same. What all other ways we have to restore the db. We have both the old data directory as well as backed up data. Shabz What is the Error? There are many ways to restore a backup and depends on how you took the backup. Plz lets us know how u took the backup and what O/S you r using --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Restore DB
Hi, We are using Postgres 8.3. We have backed up our db and while trying to restore, its not able to. We are using PgAdminIII for the same. What all other ways we have to restore the db. We have both the old data directory as well as backed up data. Shabz
[GENERAL] Using xmin to identify last modified rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Trying to identify last modified (updated or inserted) rows in a table, I thought I could use xmin. I tried is to get some lines sorted by xmin. When doing it on a slonified database, I had no problem getting these lines. But, trying the same query on a non slonified DB, I got an error, as there is no ordering operator for xid. I think that in the slon case, the query uses the implicit cast xid->xxid, and then the operator to sort xxid. What would be the best way to get last modified rows? What I tried : db=# select id_table, date_table, code_table from tb_table order by xmin desc limit 10; ERROR: could not identify an ordering operator for type xid ASTUCE : Use an explicit ordering operator or modify the query. Thanks in advance. Best regards, - -- Stéphane Schildknecht PostgreSQLFr - http://www.postgresql.fr Dalibo - http://www.dalibo.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJpTLoA+REPKWGI0ERAiCaAKCOSFQp/RtWFaLScwXLpqXQJKGzLgCgsNUn jXCUCSBBXVP7WEIn/M0Pklc= =PN5v -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large object loading stalls
Tom Lane wrote: Michael Akinde writes: Tom Lane wrote: In the past we've seen this type of thing caused by multithreaded client programs in which more than one thread tried to use the same PGconn object without adequate interlocking. Our application is single-threaded, so it seems unlikely that we are running into a problem with that. Well, maybe you've found an actual bug then; but without a test case that other people can poke at, it's hard to investigate. The only thing I can think of right now, is that we are running a Postgres 8.3 on Debian Etch (so a backported debian package), whereas the libraries linked into our application are older library version (libpq4 and libpqxx 2.6.8). Older libpq versions should work fine with an 8.3 server --- if they don't, that's a bug in itself. I do not know the status of libpqxx though; you might want to check whether there are known bugs in that version. There are a few known bugs in pqxx that *might* relate to our code; for instance, we use prepared statements, and this is an area that has seen a good deal of work from 2.6.8 to 2.6.9. The test work at the moment is hampered by our server section needing to physically move some of our development & test servers this week, but if upgrading pqxx doesn't seem to eliminate the problem, I'll do some work to see if I can recreate the problem in an isolated setting. Regards, Michael A. begin:vcard fn:Michael Akinde n:Akinde;Michael org:Meteorologisk Institutt, Norge;IT adr;quoted-printable:;;Gaustadall=C3=A9en 30D;Oslo;;0313;Norge email;internet:michael.aki...@met.no tel;work:22963379 tel;cell:45885379 x-mozilla-html:FALSE url:http://www.met.no version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore single table
On Feb 24, 2009, at 1:07 PM, Albe Laurenz wrote: Kevin Duffy wrote: I need guidance on how move some changes that I have made to my production database. A few thoughts: Maybe you do not need to delete and recreate the table. An ALTER TABLE statement can, for example, add a column to an existing table. That way you could leave the foreign key constraints in place while you do the update. If you cannot avoid dropping and recreating the table, you could proceed like this: drop all foreign key constraints to your table, recreate it and add the constraints again. You should write an SQL script that does the necessary changes and test it beforehand. Lock out all database users while you perform substantial changes to the database. You should also perform these operations in a transaction block so that you can test (in that session) whether your changes behave as expected before you commit (or rollback if they don't). Create savepoints before performing such tests so that typos in your test queries don't invalidate your schema changes. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,49a52b8b129741404319634! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] funny view/temp table problem with query
So I have a 'accounts' table, with id and name, and than some hypothetical 'packages' table, containing some info per customer. I need to retrive distinct pairs , of random packages assigned per customer. Packages table contains 10 packages, id:=[1:10], there's 1M customers for testing purposes. I could name the tables foo/bar again, but decided for something more creative this time ;) Anyways, I have this query: select count(distinct (v,id)) from ( select heh.id, v[i] from ( SELECT ss.id, ARRAY ( SELECT id FROM packages where ss.id>0 and id between 2 and 6 ORDER BY random() limit 5 ) as v FROM ( SELECT id FROM accounts ORDER BY random() limit 10 ) ss ) heh,generate_series(1, 5 ) i order by heh.id,v ) ziew; So in theory, that should return me random array of packages, per account. Since id's in both tables are primary keys, I expect the pair of accountId/packageId to be unique as well. The query above doesn't deliver, so I tried to divide it up: create view hehview as SELECT ss.id, ARRAY ( SELECT id FROM packages where ss.id>0 and id between 2 and 6 ORDER BY random() limit 5 ) as v FROM ( SELECT id FROM accounts ORDER BY random() limit 10 ) ss select count( distinct (id, v[i])) from hehview, generate_series(1, 5) i; That doesn't work either, because postgresql 'merges' view into query (which is a good way to chop large queries btw, and still keep them up to speed). But if I store intermediate result in temporary table, all values are nicely unique - as I want them. Now, that's the solution I will use. But for sake of my conciousness, I want to know what has failed here. Btw, the count(distinct(x,y)) works that way only on 8.4, but I tested it on 8.3, and I get same results. with temp table: create temp table hehtable as select * from hehview; select count( distinct (id, v[i])) from hehtable, generate_series(1, 5) i; Thanks folks. -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can I use a query with UPDATE on its SET?
Hi Ho! Since I can do: INSERT INTO table (SELECT a_transaction.*); I am wondering whether I can do: UPDATE table SET (SELECT a_transaction.*) WHERE primary_key = (SELECT a_transaction.primary_key); instead of: DELETE FROM table WHERE primary_key = (SELECT a_transaction.primary_key); INSERT INTO table (SELECT a_transaction.*); Can I? I am using PostgreSQL 8.3.5. I get the feel that I cannot do so from reading the manual. Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
2009/2/25 Karsten Hilbert > > Think backwards: How would it automatically *dis*appear from > there. Have the secondary create it and check for existance > at regular intervals. Have the primary delete it at slightly > shorter intervals. > > When the secondary finds it again after the check interval > the primary didn't delete it. Wait some more for good > measure and/or try to ping the primary some other way... > > Thanks for the suggestion. I'm wondering what the chances of such a create/delete system would have of accidentally causing failover? Is it possible to use the archive command parameter to firstly run the pg_standby command, and follow it by a touch /tmp/pg.triggerfile by using &&?
Re: [GENERAL] Warm standby failover mechanism
On Wed, Feb 25, 2009 at 09:26:26AM +, Thom Brown wrote: > We've set up a primary server in archive mode to continuously archive to an > NFS mount, and the standby server to continuously recovery from that > directory (although I'm not sure that's actually working... I've probably > overlooked something). The problem we face is working out how to tell the > standby server that it is the primary. Yes this can be done with a trigger > file in /tmp, but how would that automatically appear there? Think backwards: How would it automatically *dis*appear from there. Have the secondary create it and check for existance at regular intervals. Have the primary delete it at slightly shorter intervals. When the secondary finds it again after the check interval the primary didn't delete it. Wait some more for good measure and/or try to ping the primary some other way... Just my 2 cents, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Fri, 20 Feb 2009 20:45:20 +
Sam Mason wrote:
> On Fri, Feb 20, 2009 at 04:51:33PM +0100, Ivan Sergio Borgonovo
> wrote:
> > What I find a bit annoying is politely deal with the error once
> > it is reported back to the application *and* connection and
> > *bandwidth* costs of moving clearly wrong data back and forward.
> This sounds a bit like premature optimization to me; I don't think
Well... I'd just know how things work. Not to optimise at the
starting blocks but rather to avoid cutting my way to optimisation
later. I'm glad to learn that not only postgresql is not seriously
affected by constraints but it may take advantage of them as
suggested by Ron Mayer.
> many people worry about optimizing the failure code paths. I know
> I prefer to make sure that things go quickly when they're
> working. If you're worried about someone performing a DOS attack
> on a failure then you'd want to optimize it, but surely you'd want
> the checks early in the application code.
There may be several reasons to "duplicate" checks in the
application too. Sometimes the failure path is more frequent than
the success path, sometimes you need quick feedback, sometimes it is
a matter of bandwidth etc...
> > If you've a good mapping between pg types and the application
> > language/library types it becomes easier to keep in sync those
> > checks otherwise it is a really boring job and DB checks becomes
> > just one more security net to maintain.
> It does, but constraints like that aren't going to be changing to
> regularly are they?
Actually if I was omniscient I wouldn't be so deeply involved with
programming... but even if I was, an application may serve different
needs during its lifespan.
And still having to write constraint in the application and in the
DB is twice the work.
Furthermore a DB reports error in a way that may not be useful to
the user.
create table test.zau(a int, b int);
insert into test.zau values('z','z');
ERROR: invalid input syntax for integer: "z"
create table test.zau(a int check (a>0), b int);
insert into test.zau values(-1,5);
ERROR: new row for relation "zau" violates check constraint
"zau_a_check"
And in a less than ideal world you may be tempted to put constraints
just in the client.
Once upon a long ago I gave a look to RoR and I vaguely remember you
could define tables with constraint in ruby and somehow you
automatically had constraints in the DB and some primitive check on
the client too.
But maybe I was daydreaming.
I wonder how all this magic works once you've to refactor.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Warm standby failover mechanism
Thanks for the link Simon, but this doesn't recommend any method for triggering failover, or telling the primary that another server is now primary. We've set up a primary server in archive mode to continuously archive to an NFS mount, and the standby server to continuously recovery from that directory (although I'm not sure that's actually working... I've probably overlooked something). The problem we face is working out how to tell the standby server that it is the primary. Yes this can be done with a trigger file in /tmp, but how would that automatically appear there? And when the failed server actually restarted, or the Postgres service restarts, how do we tell it that it is no longer the primary? Thanks Thom 2009/2/24 Simon Riggs > > On Tue, 2009-02-24 at 16:55 +, Thom Brown wrote: > > > We're looking at setting up a warm-standby server using log shipping > > and aren't too sure about how we should trigger failover. Is there a > > commonly-used approach which is reliable enough to recommend? Looking > > at the documentation, there doesn't seem to be any recommendation. I > > preferrably don't want to use a witness server. > > > > Also, what would you say is the best way to tell the failed primary > > server that it is no longer the primary server? > > http://www.postgresql.org/docs/8.3/static/pgstandby.html > > -- > Simon Riggs www.2ndQuadrant.com > PostgreSQL Training, Services and Support > >
Re: [GENERAL] speaking of 8.4...
On Wed, Feb 25, 2009 at 8:16 AM, Scara Maccai wrote: > What? Hot standby won't make it in 8.4? Hot standby != synch-rep. The former is still being reviewed, though it's starting to look like it's cutting it pretty fine for inclusion in 8.4. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] where to divide application and database
On Sat, 21 Feb 2009 15:02:55 -0800 Ron Mayer wrote: > Ivan Sergio Borgonovo wrote: > > I was wondering if "checks" may have an impact > > on performances and if pg does some optimisation over them. > Are you suggesting thee would be a positive or negative impact > on performance. > Moving some checks in the database should *improve* performance > by giving the planner improved information.For example, unique > constraints indicate when only 0-1 rows may come out of a query; > and range constraints could let a database know when a partition > doesn't even need to be visited. > No doubt other checks (say, spellchecking a column) would have > have performance costs. I was wondering where and if they could have a performance impact (positive or negative). We're talking about PostgreSQL, not an abstract DB or another implementation. Would you delegate constraint check to *any other DB*? > I'm with David Fetter's perspective of considering multiple > applications that can run on top of a database. Me too. Postgresql is a mature application; the chances the most frequent useful optimisation are not already there are smaller than the mistakes I could make putting optimisations and constraints check in my application. Still it is better to know than guess. That could help in engineering the constraints differently or well to exploit better their "performance boost". -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
What? Hot standby won't make it in 8.4? That's a shame... - Messaggio originale - > Da: Fujii Masao > A: pie...@hogranch.com > Cc: pgsql-general@postgresql.org > Inviato: Martedì 24 febbraio 2009, 20:47:05 > Oggetto: Re: [GENERAL] speaking of 8.4... > > Hi, > > On Tue, Feb 24, 2009 at 5:16 AM, John R Pierce wrote: > > is it looking like the simple replication will make it into 8..4? > > You mean the built-in synchronous replication feature? If so, no. > It was decided that synch-rep will be postponed to 8.5. > > Regards, > > -- > Fujii Masao > NIPPON TELEGRAPH AND TELEPHONE CORPORATION > NTT Open Source Software Center > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Passa a Yahoo! Mail. La webmail che ti offre GRATIS spazio illimitato, antispam e messenger integrato. http://it.mail.yahoo.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
