[GENERAL] Wish: remove ancient constructs from Postgres
It is difficult to write standard-compliant code in Postgres. There are a lot of constructs which have SQL equivalents but are still used widely, even in samples in docs! For example, there are suggestions using now()::CHAR!=foo while the correct way is CAST(CURRENT_DATE AS CHAR)foo now() function, :: and != operators should be removed from language. I like the Python logic: there is one way Postgres uses C logic: there are multiple ways. Bruce seems to attempt start this process trying implement escape_string_warning in postgresql.conf . However, this is only very minor step. Please clean Postgres. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is the pg_locks been used?
On Thu, 2006-02-23 at 17:12 -0300, Carlos Henrique Reimer wrote: When the pg_locks view is used the internal lock manager data structures are momentarily locked and that is why I would like to know if some application is reading the pg_locks view and how many times. Is there a way to discover it? AFAIK there is no easy way to determine this information. You could probably patch Postgres to record the info fairly easy: one way would be to allocate a small block of shared memory and an LWLock to protect it, and then have pg_lock_status() acquire the lock and increment a counter. Then add a new function to retrieve the current value of the counter. You could even do it without modifying the backend proper: change the definition of the pg_locks view to invoke a set-returning PL/PgSQL function. That function would increment a counter stored in some table, and then construct and return the normal pg_locks result set. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Wish: remove ancient constructs from Postgres
As long as the SQL standard is supported, support for the ancient syntax shouldn't be removed - at least not without a very long period of transition. Do you have any idea how many applications the removal of something simple like the cast operator :: will break? It's not difficult to write standard-compliant code in PostgeSQL - just feel free to use the standard. Nobody forces you to use the uncomliant syntax - but don't try to force thousands of people to rewrite tons of code just because you don't like the old syntax. Oracle has stuff like that, DB2 has too. Things evolve over time and unless you give code-maintainers ample time to fix their applications a sudden drop of old constructs will potentially just harm the project's popularity. I agree with you that the docs and examples should be amended to show standard constructs, just to encourage the adoption of standard compliant statements. Feel free to volunteer some time to make this happen - I'm confident everyone in the community will appreciate it. UC On Sunday 26 February 2006 00:36, Andrus Moor wrote: It is difficult to write standard-compliant code in Postgres. There are a lot of constructs which have SQL equivalents but are still used widely, even in samples in docs! For example, there are suggestions using now()::CHAR!=foo while the correct way is CAST(CURRENT_DATE AS CHAR)foo now() function, :: and != operators should be removed from language. I like the Python logic: there is one way Postgres uses C logic: there are multiple ways. Bruce seems to attempt start this process trying implement escape_string_warning in postgresql.conf . However, this is only very minor step. Please clean Postgres. Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres Win32 libpq Samples
Hello, I am very new to postgreSQL I am using the win32 platform is there a website URL that has Sample Client Apps written for win32? I need Samples of very basic things like: Connect to a db on a remote server create new db create table add records drop records ect... there are some Samples here http://www.postgresql.org/docs/8.1/static/libpq-example.html but they will not compile on win32 using VC++ 7 I see no particular reason why those samples wouldn't work in VC++. Exactly what errors do you get? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] psql client binary
Hopefully not to grievous an FAQ: can anybody point me at a precompiled binary of the psql terminal-based front-end to run on NT hopefully not requiring Cygwin, or has this been effectively replaced by pgAdmin? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] psql client binary
Mark Morgan Lloyd wrote: Hopefully not to grievous an FAQ: can anybody point me at a precompiled binary of the psql terminal-based front-end to run on NT hopefully not requiring Cygwin, or has this been effectively replaced by pgAdmin? pginstaller has such a psql binary that should work on NT, but it will not install on NT. See the FAQ: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(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] Postgre capability
Hi All, I'm looking for anyexperience on runing an ERP software (Oracle application, SAP, PeopleSoft, ...) on top of a postgre data base. Best Retards Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
[GENERAL] Stored procedure doesn't return expected result.
I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.
create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
$$ LANGUAGE SQL;
However, when I do this:
select get_client_id('Stuart Grimshaw');
I get no results, yet:
SELECT intclientid FROM client WHERE vchname = 'Stuart Grimshaw';
Gives me the result I would expect:
intclientid
-
3
What am I doing wrong in the stored procedure?
--
-S
http://www.makepovertyhistory.org/
---(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] ECPG and COPY
Yes, it's still an open report. Sorry, about that and all the other open bug reports/feature requests. I do not have the time at the moment to even reproduce bugs. Hopefully this will become better in the near future. Should we add this to TODO? Anything else? Yes, please add it. I do have some more open bug reports/feature requests, but I'd like to reproduce things first before we add to the docs. Sometimes it's easier to fix it than to add it. :-) Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Stored procedure doesn't return expected result.
Stuart Grimshaw [EMAIL PROTECTED] schrieb:
I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.
create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
$$ LANGUAGE SQL;
However, when I do this:
select get_client_id('Stuart Grimshaw');
I get no results, yet:
Please read our documentation about executing dynamic commands:
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Stored procedure doesn't return expected result.
Stuart Grimshaw [EMAIL PROTECTED] schrieb:
I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.
create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
^ ^
remove the '
test=# select * from foo1;
x | i
---+---
a | 1
b | 2
(2 rows)
test=# create or replace function get_i(varchar) returns int as $$
select i from foo1 where x = $1;$$ language sql;
CREATE FUNCTION
test=# select get_i('a');
get_i
---
1
(1 row)
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.(unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Stored procedure doesn't return expected result.
On 2/26/06, Andreas Kretschmer [EMAIL PROTECTED] wrote:
Stuart Grimshaw [EMAIL PROTECTED] schrieb:
I'm writing a script to clean up some data in a table, the data I'm
using as the source is held in emails, so I've written a perl script
to extract the info. Unfortunatly this email doesn't contain the
client id, so I've written a stored procedure to extract it.
create or replace function get_client_id(text) returns integer as $$
SELECT intclientid FROM client WHERE vchname = '$1';
^ ^
remove the '
test=# select * from foo1;
x | i
---+---
a | 1
b | 2
(2 rows)
test=# create or replace function get_i(varchar) returns int as $$
select i from foo1 where x = $1;$$ language sql;
CREATE FUNCTION
test=# select get_i('a');
get_i
---
1
(1 row)
That's got it. Obviously it understands that $1 is a string and not a
column name.
Thanks very much.
--
-S
http://www.makepovertyhistory.org/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] psql client binary
Bruce Momjian wrote: Mark Morgan Lloyd wrote: Hopefully not to grievous an FAQ: can anybody point me at a precompiled binary of the psql terminal-based front-end to run on NT hopefully not requiring Cygwin, or has this been effectively replaced by pgAdmin? pginstaller has such a psql binary that should work on NT, but it will not install on NT. See the FAQ: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html Thanks Bruce. So in general NT4 is out as a client and I might not even be able to install pgAdmin as a binary. Bit of a bother that- I'm hoping to get clients and users onto Linux RSN but until then being able to e.g. cut-and-paste out of psql is something I find very useful indeed. Alternatively is there a binary somewhere of the psql client that uses the Cygwin DLL? Apologies for sounding like a total wimp, I'm an embedded-systems guy who also does Delphi on PCs. I'm up to a bit of C hacking under Linux but have never dirtied my hands with C or C++ on Win-32. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] psql client binary
Hopefully not to grievous an FAQ: can anybody point me at a precompiled binary of the psql terminal-based front-end to run on NT hopefully not requiring Cygwin, or has this been effectively replaced by pgAdmin? pginstaller has such a psql binary that should work on NT, but it will not install on NT. See the FAQ: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html Thanks Bruce. So in general NT4 is out as a client and I might not even be able to install pgAdmin as a binary. Bit of a bother that- I'm hoping to get clients and users onto Linux RSN but until then being able to e.g. cut-and-paste out of psql is something I find very useful indeed. The client should work on NT4. It's the server that has some problems. And th einstlaler itself. But if you use the binaries-no-installer distribution (it's on the ftp site), you should be able to use it. Not sure about pgAdmin. Alternatively is there a binary somewhere of the psql client that uses the Cygwin DLL? I think there may be on ein the actual Cygwin distribution, but I'm not sure - it may also be an old verison. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] psql client binary
-Original Message- From: Mark Morgan Lloyd[EMAIL PROTECTED] Sent: 26/02/06 14:33:55 To: pgsql-general@postgresql.orgpgsql-general@postgresql.org Subject: Re: [GENERAL] psql client binary Thanks Bruce. So in general NT4 is out as a client and I might not even be able to install pgAdmin as a binary. Use the no-installer win32 package (it's just a zip of the core server files), psql should run on any version of windows from 95. You'll need some of the dlls as well - from memory: Libpq.dll Libintl.dll Libiconv.dll Comerr32.dll Krb5_32.dll Libeay32.dll Ssleay32.dll And pgAdmin /should/ work as well, but I don't know if anyone has tried it on NT4 in a while. Regards, Dave -Unmodified Original Message- Bruce Momjian wrote: Mark Morgan Lloyd wrote: Hopefully not to grievous an FAQ: can anybody point me at a precompiled binary of the psql terminal-based front-end to run on NT hopefully not requiring Cygwin, or has this been effectively replaced by pgAdmin? pginstaller has such a psql binary that should work on NT, but it will not install on NT. See the FAQ: http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html Thanks Bruce. So in general NT4 is out as a client and I might not even be able to install pgAdmin as a binary. Bit of a bother that- I'm hoping to get clients and users onto Linux RSN but until then being able to e.g. cut-and-paste out of psql is something I find very useful indeed. Alternatively is there a binary somewhere of the psql client that uses the Cygwin DLL? Apologies for sounding like a total wimp, I'm an embedded-systems guy who also does Delphi on PCs. I'm up to a bit of C hacking under Linux but have never dirtied my hands with C or C++ on Win-32. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgre capability
On Sun, Feb 26, 2006 at 04:55:21AM -0800, Farhad wrote: Hi All, I'm looking for any experience on runing an ERP software (Oracle application, SAP, PeopleSoft, ...) on top of a postgre data base. The database is called PostgreSQL or Postgres, not Postgre. Search for ERP and Postgres on http://sourceforge.net and you'll get some hits back. I don't know of anyone running a commercial ERP system on PostgreSQL, but that doesn't mean someone isn't doing it. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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] Wish: remove ancient constructs from Postgres
Andrus Moor [EMAIL PROTECTED] writes: For example, there are suggestions using now()::CHAR!=foo while the correct way is CAST(CURRENT_DATE AS CHAR)foo now() function, :: and != operators should be removed from language. Your second example requires twice as much typing as your first; is it so surprising that some people prefer the shorter way? We'd consider removing these features if they were actually blocking support of some spec-required behavior ... but since they don't, it's quite unlikely they'll ever be removed. Feel free not to use them if you don't like them, but don't try to impose your viewpoint on everyone else. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Sun, Feb 26, 2006 at 10:36:23AM +0200, Andrus Moor wrote: It is difficult to write standard-compliant code in Postgres. There are a lot of constructs which have SQL equivalents but are still used widely, even in samples in docs! For example, there are suggestions using ... Bruce seems to attempt start this process trying implement escape_string_warning in postgresql.conf . However, this is only very minor step. Please clean Postgres. Please help. Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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] createuser permssion for group
chris smith [EMAIL PROTECTED] writes: On 2/26/06, Jebus [EMAIL PROTECTED] wrote: Is it possible to give a group the the createuser permission ? This way if a user in the group they can create users. Version 8.1.x does.. Note that even in 8.1, the special privileges like CREATEROLE aren't implicitly inherited via group membership. You can use them but you have to explicitly SET ROLE to the group that has 'em. Example: regression=# create group admin createrole; CREATE ROLE regression=# create user tgl in group admin; CREATE ROLE regression=# \c - tgl You are now connected as new user tgl. regression= create user foo; ERROR: permission denied to create role regression= set role admin; SET regression= create user foo; CREATE ROLE For more info see http://www.postgresql.org/docs/8.1/static/role-membership.html regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Management of Concurrent Clients
Hi all, I am working on an application that involves multiple processes accessing and updating different databases: tables are split into 3 different databases that are accessed by 3 different processes. This architectural decision was made in the past because it has been noticed that former revisions of postgres used to lock the whole database when a process performs an update (on a record) which prevents the other processes from accessing the database. After some readings on postgres concurrent client management, I am planning on changing the database structure to make all application processes access a shared database (i.e. put all application tables in a single database that would be shared among the 3 processes). I did not find in the documentation (at least in the current versions of postgres starting from revision 7) any specific problem related to doing this, comments on this problem will be appreciated. Thanks! Hanan
Re: [GENERAL] Management of Concurrent Clients
Hanan Bentaleb schrieb: Hi all, I am working on an application that involves multiple processes accessing and updating different databases: tables are split into 3 different databases that are accessed by 3 different processes. This architectural decision was made in the past because it has been noticed that former revisions of postgres used to lock the whole database when a process performs an update (on a record) which prevents the other processes from accessing the database. Which former revision should that have been? Was it postgres95 or was it postgresql yet? After some readings on postgres concurrent client management, I am planning on changing the database structure to make all application processes access a shared database (i.e. put all application tables in a single database that would be shared among the 3 processes). I did not find in the documentation (at least in the current versions of postgres starting from revision 7) any specific problem related to doing this, comments on this problem will be appreciated. Its very unclear what kind of problems you expect. Accessing postgresql with lots of clients is a common practice. You can also use schemas to isolate your applications in the same database if you need that. If you want better advice, give more details on what your application does and what exact problems you expect. Regards Tino ---(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] From ASCII to UTF-8
Clodoaldo Pinto wrote: As part of a migration from 8.0 to 8.1 i want to convert the data from ASCII to UTF-8. I dumped the database with pg_dump (8.0) and tried to convert it with iconv, but it shows an error: $ iconv -t ASCII -t UTF-8 fahstats_data.dump -o fahstats_data_utf-8.dump iconv: illegal input sequence at position 71407864 That position contains the decimal value 233: $ od -A d -j 71407864 -N 1 -t u1 fahstats_data.dump 71407864 233 71407865 I could use pg_dump -E in 8.1 but it is in another machine with ADSL connection and the dump size is 1.8GB. It would take more than 12 hours. How to install pg_dump 8.1 only? I tried to copy the executable and the libs but it did not work. from what you wrote it seems that your dump contains non-ascii characters... probably somehow non-ascii data got into your database. like iso-8859-1 or iso-8859-15 or cp-1252 (if you are using western-european stuff). in those encodings, 255 = é. maybe you could try something like: iconv -f ISO-8859-1 -t UTF-8 please note that a conversion FROM these encodings always succeeds. so a success does not mean that you guessed the charset correctly. you still will havet to check manually if the resulting document contains the correct data. gabor ---(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] ECPG and COPY and PQputCopyData - don't get errors
Wes [EMAIL PROTECTED] writes: I sent the column headers as the first line of the input. While this I now know this is wrong, the problem here is that I got no error response back. ... However, every call to PQputCopyData, as well as PQputCopyEnd returned a result of 1. That just indicates that the data was sent to the backend without error. You forgot to check the copy command result afterward. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How much clustered?
Bruce Momjian pgman@candle.pha.pa.us writes: Currently we output the ctid as a string: snprintf(buf, sizeof(buf), (%u,%u), blockNumber, offsetNumber); Perhaps someday we should consider outputting that as an array or a result set: It's not an array, because the two components are not of the same data type; and it's not a result set, any more than (say) a point or a box is. What it is is a record datatype. There might be some usefulness to adding SQL functions to allow extraction of the block number and item number fields, though we'd have some problems with the lack of a uint4 datatype to represent the block number field's type. The demand for this has been too low to make me feel we need to expend that effort... 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
[GENERAL] majordomo unmaintained, postmaster emails ignored?
Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. I sent an email to [EMAIL PROTECTED] more than two months ago, also without any reaction. What should I do to spark someone's interest? Pls cc me on replies. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Another perplexity with PG rules
Ken Winter [EMAIL PROTECTED] writes: After trying about a million things, I'm wondering about the meaning of OLD. as the actions in a rule are successively executed. What I have done assumes that: ... (b) The OLD. values that appear in the second (INSERT) action in the rule are not changed by the execution of the first (UPDATE) rule. I believe this is mistaken. OLD is effectively a macro for the existing row(s) satisfying the rule's WHERE clause. You've got two problems here --- one is that the UPDATE may have changed the data in those rows, and the other is that the UPDATE may cause them to not satisfy the WHERE clause anymore. (c) Whatever the truth of the above assumptions, the second (INSERT) action in the 'on_update_2_preserve_h' rule should insert SOMEthing. See above. If no rows remain satisfying WHERE, nothing will happen. How to make this whole thing do what is required? I'd suggest seeing if you can't do the INSERT first then the UPDATE. This may require rethinking which of the two resulting rows is the historical one and which the updated one, but it could probably be made to work. Also, you might think about keeping the historical info in a separate table (possibly it could be an inheritance child of the master table). This would make it easier to distinguish the historical and current info when you need to. Lastly, I'd advise using triggers not rules wherever you possibly can. In particular, generation of the historical-log records would be far more reliable if implemented as an AFTER UPDATE trigger on the base table. (Over the years I've gotten less and less satisfied with Postgres' rules feature --- it just seems way too hard to make it do what people want reliably. I'm afraid there's not much we can do to fix it without creating an enormous compatibility problem unfortunately :-(. But by and large, triggers are a lot easier for people to wrap their brains around, once they get over the notational hurdle of having to write a trigger function. I'd like to see us allow triggers on views, and then maybe rules could fade into the sunset for any but the most abstruse applications.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? A copy of the message I sent to [EMAIL PROTECTED] last december is attached. I think it has all the evidence of approval being required you need. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---BeginMessage--- Hello, I tried to subscribe to the doc mailing list on July 27th, with token 9E0C-7AF7-36CA. The request has been processed up to : accept 9E0C-7AF7-36CA : The accept command for token 9E0C-7AF7-36CA succeeded, : but further approval is needed. : : Now the request must be approved by the moderators. : The results will be mailed to you after this is done. : : : : Valid commands processed: 1 : 0 succeeded, 1 stalled, and 0 failed. : : Use the following command: : sessioninfo d9a46448c54d3b48ad049e79e4c46dbaa9ee79f7 : to see technical information about this session. Nothing happened since then. I also tried to subscribe to general, hackers, perf, and sql on September 24th, with tokens CA86-08AC-51A7, 225E-80E3-7C1A, B43A-4209-5756, and 74A6-0E96-E08F, with exactly the same result. Could someone point please process the requests? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---End Message--- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgre capability
Hi, Thanks for the link. More exactly I wanted to know how far PostgreSql is from the Oracle or DB2 regard the following point: Performances, Data volume management, Could PostgreSql handle the giga data? Backup and restore functionality.Thanks Farhad"Jim C. Nasby" [EMAIL PROTECTED] wrote: On Sun, Feb 26, 2006 at 04:55:21AM -0800, Farhad wrote: Hi All, I'm looking for any experience on runing an ERP software (Oracle application, SAP, PeopleSoft, ...) on top of a postgre data base. The database is called PostgreSQL or Postgres, not Postgre.Search for ERP and Postgres on http://sourceforge.net and you'll getsome hits back. I don't know of anyone running a commercial ERP systemon PostgreSQL, but that doesn't mean someone isn't doing it.-- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]Pervasive Software http://pervasive.com work: 512-231-6117vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461---(end of broadcast)---TIP 9: In versions be low 8.0, the planner will ignore your desire tochoose an index scan if your joining column's datatypes do notmatch Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: [GENERAL] Management of Concurrent Clients
Hi Tino, Thanks for your response! I did not provide more specific technical details because I just came in to the project. So, I will try to provide as much information as I can. Former versions of postgresql used in this project are postgresql (starting at 6). I will try to find out the exact revision number. It has been reported to me that the main problem encountered with former postgresql versions is that when a process performs an update (of a record in any table), the whole database was locked which prevents the other processes from accessing the database (to retrieve data for instance). In other words, the process performing the update operation had an exclusive access to the database. This had an important impact on the system performance and justified the fact that tables were spread into different databases. Usually, granularity on locks is at the row or table level but maybe there was a specific configuration that was used which made it so that the entire database was locked. Before starting any major database re-structuring, I want to make sure that are not specific cases where this situation occurs. Thanks! Hanan -Original Message- From: Tino Wildenhain [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 12:54 PM To: Hanan Bentaleb Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Management of Concurrent Clients Hanan Bentaleb schrieb: Hi all, I am working on an application that involves multiple processes accessing and updating different databases: tables are split into 3 different databases that are accessed by 3 different processes. This architectural decision was made in the past because it has been noticed that former revisions of postgres used to lock the whole database when a process performs an update (on a record) which prevents the other processes from accessing the database. Which former revision should that have been? Was it postgres95 or was it postgresql yet? After some readings on postgres concurrent client management, I am planning on changing the database structure to make all application processes access a shared database (i.e. put all application tables in a single database that would be shared among the 3 processes). I did not find in the documentation (at least in the current versions of postgres starting from revision 7) any specific problem related to doing this, comments on this problem will be appreciated. Its very unclear what kind of problems you expect. Accessing postgresql with lots of clients is a common practice. You can also use schemas to isolate your applications in the same database if you need that. If you want better advice, give more details on what your application does and what exact problems you expect. Regards Tino ---(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] Management of Concurrent Clients
Hanan Bentaleb schrieb: Hi Tino, Thanks for your response! I did not provide more specific technical details because I just came in to the project. So, I will try to provide as much information as I can. Former versions of postgresql used in this project are postgresql (starting at 6). I will try to find out the exact revision number. It has been reported to me that the main problem encountered with former postgresql versions is that when a process performs an update (of a record in any table), the whole database was locked which prevents the other processes from accessing the database (to retrieve data for instance). In other words, the process performing the update operation had an exclusive access to the database. This had an important impact on the system performance and justified the fact that tables were spread into different databases. Usually, granularity on locks is at the row or table level but maybe there was a specific configuration that was used which made it so that the entire database was locked. Before starting any major database re-structuring, I want to make sure that are not specific cases where this situation occurs. I wonder how they managed to lock the whole database. You can try and lock a table but usually its virtually unlocked (thanks to MVCC). And even 6.x had MVCC. You schould give us an example what they do. Otherwise it sounds like a myth :) Regards Tino ---(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] Postgre capability
[EMAIL PROTECTED] (Farhad) wrote: I'm looking for any experience on runing an ERP software (Oracle application, SAP, PeopleSoft, ...) on top of a postgre data base. You won't find it, for two reasons: 1. There's no such thing as postgre The proper name is PostgreSQL, though people are often forgiven for falling back to postgres. 2. Looking at that list... - Oracle Applications are an Oracle product, written expressly for the Oracle database platform. - SAP is not the identity of a software product; it is the name of a large German company that sells something known as R/3. Deploying R/3 to additional database platforms (I *believe* the current list of databases is Oracle, Informix, DB2, Microsoft SQL Server, and SAP-DB) requires that SAP AG rewrite portions of the R/3 kernel. You cannot run R/3 atop any database you choose; you must run it on one of the specific combinations of OS and database that SAP AG supports. (They actually get more precise than that; historically, you needed to use a database install that was bundled with R/3. Thus, an R/3 on HP/UX and Oracle installation was a set of CDs that included both R/3 and Oracle, and that would only run on a specific release of HP/UX...) -- output = reverse(moc.liamg @ enworbbc) http://linuxdatabases.info/info/internet.html Including a destination in the CC list that will cause the recipients' mailer to blow out is a good way to stifle dissent. -- from the Symbolics Guidelines for Sending Mail ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-26 20:15:20 +0100: # [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? A copy of the message I sent to [EMAIL PROTECTED] last december is attached. I think it has all the evidence of approval being required you need. Please do let me know if I need to submit the subscription requests identified in the email again. I'd really appreciate if someone did help me through this. BTW, my first never replied to email to people who should take care of majordomo I could find is a mail sent to [EMAIL PROTECTED] on Wed, 3 Aug 2005 00:40:15 +0200 (Message-ID: [EMAIL PROTECTED]). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ECPG and COPY and PQputCopyData - don't get errors
On 2/26/06 12:12 PM, Tom Lane [EMAIL PROTECTED] wrote: That just indicates that the data was sent to the backend without error. You forgot to check the copy command result afterward. Ok, after re-reading the docs for the 10th time, I see I missed that I need to paragraph about calling 'PQgetResult' after the PQputCopyEnd. I was just checking the result and PQerrorMessage. I think what threw me off was: If a COPY command is issued via PQexec in a string that could contain additional commands, the application must continue fetching results via PQgetResult after completing the COPY sequence. Only when PQgetResult returns NULL is it certain that the PQexec command string is done and it is safe to issue more commands. (looked like this was needed only if multiple commands were in the Pqexec) and PQgetResult Waits for the next result from a prior PQsendQuery, PQsendQueryParams, PQsendPrepare, or PQsendQueryPrepared call, and returns it. (doesn't mention COPY). I have it working now. Thanks. Wes ---(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] Management of Concurrent Clients
Tino Wildenhain [EMAIL PROTECTED] writes: Hanan Bentaleb schrieb: It has been reported to me that the main problem encountered with former postgresql versions is that when a process performs an update (of a record in any table), the whole database was locked I wonder how they managed to lock the whole database. I'd believe table-level locks; we used those in *really* old versions of Postgres. (According to the release notes, MVCC was added in PG 6.5 released 1999-06-09.) I don't believe there ever was a facility that would perform database-level locking at all. Most PG hackers would call you certifiably insane if you were still using a pre-MVCC version today. On data reliability grounds alone, anything older than 7.2 is simply unsafe because of the XID wraparound problem (let alone plain old bugs, of which there were many). If you check the release history you will notice that 7.2.* was the first release series that we continued to update after the initial release of the next series. This is not coincidental: it reflects community judgment that 7.2 was the first release series you'd really want to use for long-term production purposes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Sun, 2006-02-26 at 12:08 -0500, Tom Lane wrote: We'd consider removing these features if they were actually blocking support of some spec-required behavior ... but since they don't, it's quite unlikely they'll ever be removed. Right; there are plenty of places in which PostgreSQL extends the standard. If you're concerned about writing standard-compliant applications, merely removing the places where we have historical syntax variants is probably going to be of little help. I think a better approach would be to introduce the concept of SQL dialects, similar to --std=... in GCC or SQL modes in MySQL 5. That would help people who want to write standard-compliant applications while not inconveniencing those who don't care. -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgre capability
It is certainly capable of running an ERP system from a technical standpoint,
just that the major commercial vendors don't support it in their
applications. If you were writing your own system though (or if you wanted
to port one), you should be fine.
On Sunday 26 February 2006 14:19, Farhad wrote:
Hi,
Thanks for the link.
More exactly I wanted to know how far PostgreSql is from the Oracle or
DB2 regard the following point: Performances,
Data volume management, Could PostgreSql handle the giga data?
Backup and restore functionality.
Thanks Farhad
Jim C. Nasby [EMAIL PROTECTED] wrote:
On Sun, Feb 26, 2006 at 04:55:21AM -0800, Farhad wrote:
Hi All,
I'm looking for any experience on runing an ERP software (Oracle
application, SAP, PeopleSoft, ...) on top of a postgre data base.
The database is called PostgreSQL or Postgres, not Postgre.
Search for ERP and Postgres on http://sourceforge.net and you'll get
some hits back. I don't know of anyone running a commercial ERP system
on PostgreSQL, but that doesn't mean someone isn't doing it.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] Wish: remove ancient constructs from Postgres
On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] wrote: Please help. how? is there any place where postgres' SQL:2003 incompatibilities are being discussed? I really want to have standard-compatible PostgreSQL and some option in postgresql.conf that would allow me to restrict Postgres' SQL syntax to standard. The suggestion 'to feel free and use only standard queries' is the bad thing, because: a. the papers of SQL:2003 are quite hard to understand, even for good specialist (the main part, #2 has more than 1300 pages!) b. what about novices? it's almost impossible to go the right way for them. PostgreSQL has very-very good documentation, but it teaches to go Pg's way, which is not right in that sense, unfortunately... Now we have a lot of incompatibilities. I would classify them: 1. 'Extending' features - things that offer the same abilities that standard constructions. Some of these things allow to use shorter syntax, but I really think that many of them are just 'heritage of the past'. Yes, standard is 'talkative', but I prefer only standard things, because it helps me to understand other databases and 'academical things'. Actually, I hate ':=', '::', 'INT2', etc, and really want to be able to deprecate them (via conf or something) 2. Features that are implemented in non-standard way (ot things that are not yet implemented but could be considered as basic...) The good examples are: ILIKE and lack of ability to set up collation (rules for string comparison); lack of NULLS FIRST / LAST construction and necessity to add additional ordering step to ORDER BY instead of that. 3. 'Ugly' things like DISTINCT ON expression [, ...] (see http://chernowiki.ru/index.php?node=38#A13) Maybe to create a sub-project (or special section in TODO) for improving SQL:2003 compatibility? I've encountered with many 'reefs' during migration from MS SQL to Postgres. Some of them are here: http://chernowiki.ru. I do think that such drawbacks complicate migration for other DBMSs' guys and understanding SQL for newbies. -- Best regards, Nikolay ---(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] majordomo unmaintained, postmaster emails ignored?
'k, I just checked all the lists you listed, and you are subscribed to each of them ... are you not receiving messages? On Sun, 26 Feb 2006, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? A copy of the message I sent to [EMAIL PROTECTED] last december is attached. I think it has all the evidence of approval being required you need. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Wish: remove ancient constructs from Postgres
Nikolay Samokhvalov wrote: On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] wrote: Please help. how? 1. Pick an item on the list. 2. Join the -hackers list and ask for info on where to start and discuss what you want to change. 3. Start coding. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Which indexes does a query use?
In pg v7.4.5, I have this query:- select * from activities where activity_user_id = 2 and activity_type = 1 and activity_ts now() order by activity_ts desc limit 1; where activity_user_id is a non-unique index and activity_ts is a non-unique index. When I explain it, I get:- Limit (cost=46.33..46.34 rows=1 width=58) - Sort (cost=46.33..46.34 rows=1 width=58) Sort Key: activity_ts - Seq Scan on activities (cost=0.00..46.32 rows=1 width=58) Filter: ((activity_user_id = 2) AND (activity_type = 1) AND ((activity_ts)::timestamp with time zone now())) If I'm reading this right, it's telling me that is NOT using any indexes. Clearly, this is wrong. I would have expected that index on activity_user_id would have been used to help find all the records efficiently. Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Which indexes does a query use?
Chris Velevitch wrote: In pg v7.4.5, I have this query:- select * from activities where activity_user_id = 2 and activity_type = 1 and activity_ts now() order by activity_ts desc limit 1; where activity_user_id is a non-unique index and activity_ts is a non-unique index. When I explain it, I get:- Limit (cost=46.33..46.34 rows=1 width=58) - Sort (cost=46.33..46.34 rows=1 width=58) Sort Key: activity_ts - Seq Scan on activities (cost=0.00..46.32 rows=1 width=58) Filter: ((activity_user_id = 2) AND (activity_type = 1) AND ((activity_ts)::timestamp with time zone now())) If I'm reading this right, it's telling me that is NOT using any indexes. Clearly, this is wrong. I would have expected that index on activity_user_id would have been used to help find all the records efficiently. Not necessarily. How many rows are there in the table at the moment. If pg uses and index, it first has to get the index page, then get the heap page. So if you have a small number of blocks in the heap it's actually cheaper to just scan the heap. I would guess the heap is small by the fact that the seq scan only find one row, and finds it in 46.32 units. The row width isn't high either and that means you get good block packing. Probably 80-100 row's per block. If you post explain analyze instead of explain and possibly the number row in that table, we might be able to help further, but that is my best guess from the information given. Regards Russell Smith Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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 much clustered?
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Currently we output the ctid as a string: snprintf(buf, sizeof(buf), (%u,%u), blockNumber, offsetNumber); Perhaps someday we should consider outputting that as an array or a result set: It's not an array, because the two components are not of the same data type; and it's not a result set, any more than (say) a point or a box is. What it is is a record datatype. There might be some usefulness to adding SQL functions to allow extraction of the block number and item number fields, though we'd have some problems with the lack of a uint4 datatype to represent the block number field's type. The demand for this has been too low to make me feel we need to expend that effort... Agreed, I was just pointing out that someday it might need improvement. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(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] Which indexes does a query use?
Russell Smith [EMAIL PROTECTED] writes: Chris Velevitch wrote: If I'm reading this right, it's telling me that is NOT using any indexes. Clearly, this is wrong. Not necessarily. ... If you post explain analyze instead of explain and possibly the number row in that table, we might be able to help further, but that is my best guess from the information given. Also, has the table been vacuumed/analyzed lately? The planner clearly thinks it is small, but that information could be out of date (and 7.x releases aren't going to notice if the table has gotten bigger since the last vacuum or analyze). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Fwd: [GENERAL] Which indexes does a query use?
On 2/27/06, Russell Smith [EMAIL PROTECTED] wrote: Not necessarily. How many rows are there in the table at the moment. If pg uses and index, it first has to get the index page, then get the heap page. So if you have a small number of blocks in the heap it's actually cheaper to just scan the heap. I would guess the heap is small by the fact that the seq scan only find one row, and finds it in 46.32 units. The row width isn't high either and that means you get good block packing. Probably 80-100 row's per block. It's about 200 records. Are you saying that the strategy pg uses is dynamic, in that as the size of the table grows the strategy changes? (here is the explain analyze output) Limit (cost=46.33..46.34 rows=1 width=58) (actual time=2.000..2.000 rows=1 loops=1) - Sort (cost=46.33..46.34 rows=1 width=58) (actual time=2.000..2.000 rows=1 loops=1) Sort Key: activity_ts - Seq Scan on activities (cost=0.00..46.32 rows=1 width=58) (actual time=0.000..2.000 rows=207 loops=1) Filter: ((activity_user_id = 2) AND (activity_type = 1) AND ((activity_ts)::timestamp with time zone now())) Total runtime: 3.000 ms (6 rows) Chris -- Chris Velevitch Manager - Sydney Flash Platform Developers Group www.flashdev.org.au ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: Fwd: [GENERAL] Which indexes does a query use?
Chris Velevitch wrote: On 2/27/06, Russell Smith [EMAIL PROTECTED] wrote: Not necessarily. How many rows are there in the table at the moment. If pg uses and index, it first has to get the index page, then get the heap page. So if you have a small number of blocks in the heap it's actually cheaper to just scan the heap. I would guess the heap is small by the fact that the seq scan only find one row, and finds it in 46.32 units. The row width isn't high either and that means you get good block packing. Probably 80-100 row's per block. It's about 200 records. Are you saying that the strategy pg uses is dynamic, in that as the size of the table grows the strategy changes? Yes, it does. For 200 rows, it's just as quick for it to look at every row. If you have 20,000 - then that's a different case. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Another perplexity with PG rules
Tom ~ Thanks ever so much for - again - helping me get unstuck. See comments and results inserted below. ~ Ken -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Sunday, February 26, 2006 1:47 PM To: [EMAIL PROTECTED] Cc: PostgreSQL pg-general List Subject: Re: [GENERAL] Another perplexity with PG rules Ken Winter [EMAIL PROTECTED] writes: After trying about a million things, I'm wondering about the meaning of OLD. as the actions in a rule are successively executed. What I have done assumes that: ... (b) The OLD. values that appear in the second (INSERT) action in the rule are not changed by the execution of the first (UPDATE) rule. I believe this is mistaken. OLD is effectively a macro for the existing row(s) satisfying the rule's WHERE clause. You've got two problems here --- one is that the UPDATE may have changed the data in those rows, and the other is that the UPDATE may cause them to not satisfy the WHERE clause anymore. I was afraid of this. Your conclusions do seem to fit my results. (c) Whatever the truth of the above assumptions, the second (INSERT) action in the 'on_update_2_preserve_h' rule should insert SOMEthing. See above. If no rows remain satisfying WHERE, nothing will happen. Yep, that's what was happening. How to make this whole thing do what is required? I'd suggest seeing if you can't do the INSERT first then the UPDATE. This may require rethinking which of the two resulting rows is the historical one and which the updated one, but it could probably be made to work. Yes, I had already had it working with such a scheme. It expired the existing record, and then inserted a new record with the updated values. However this scheme seemed to be causing troubles with other triggers on the base tables. That's why I was trying to recast it into a scheme that updated the existing record and then inserted a new record containing the old data. Also, you might think about keeping the historical info in a separate table (possibly it could be an inheritance child of the master table). This would make it easier to distinguish the historical and current info when you need to. I've been striving mightily to avoid taking this path, because it threatens to hopelessly complicate my foreign keys. Lastly, I'd advise using triggers not rules wherever you possibly can. In particular, generation of the historical-log records would be far more reliable if implemented as an AFTER UPDATE trigger on the base table. This appears to be the WINNER! I eliminated the INSERT action from my UPDATE rule: CREATE OR REPLACE RULE on_update_2_preserve_h AS ON UPDATE TO person ... DO ( /* Update the current H record and make it effective as of either now (if no effective date was provided) or whenever the update query specifies.*/ UPDATE person_h SET person_id = NEW.person_id, first_name = NEW.first_name, middle_names = NEW.middle_names, last_name_prefix = NEW.last_name_prefix, last_name = NEW.last_name, name_suffix = NEW.name_suffix, preferred_full_name = NEW.preferred_full_name, preferred_business_name = NEW.preferred_business_name, user_name = NEW.user_name, _action = NEW._action, effective_date_and_time = CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP -- Query assigned no value ELSE NEW.effective_date_and_time -- Query assigned value END WHERE person_id = OLD.person_id AND effective_date_and_time = OLD.effective_date_and_time ; /* Copy the old values to a new record. Expire it either now (if no effective date was provided) or whenever the update query specifies.*/ INSERT INTO person_h ( person_id, first_name, middle_names, last_name_prefix, last_name, name_suffix, preferred_full_name, preferred_business_name, user_name, _action, effective_date_and_time, expiration_date_and_time) VALUES ( OLD.person_id, OLD.first_name, OLD.middle_names, OLD.last_name_prefix, OLD.last_name, OLD.name_suffix, OLD.preferred_full_name, OLD.preferred_business_name, OLD.user_name, OLD._action, OLD.effective_date_and_time, CASE WHEN NEW.effective_date_and_time = OLD.effective_date_and_time THEN CURRENT_TIMESTAMP-- Query assigned no value ELSE NEW.effective_date_and_time-- Query assigned a value END) ;
Re: [GENERAL] Wish: remove ancient constructs from Postgres
On Mon, Feb 27, 2006 at 00:25:57 +0300, Nikolay Samokhvalov [EMAIL PROTECTED] wrote: On 2/26/06, Karsten Hilbert [EMAIL PROTECTED] wrote: past'. Yes, standard is 'talkative', but I prefer only standard things, because it helps me to understand other databases and 'academical things'. Actually, I hate ':=', '::', 'INT2', etc, and really want to be able to deprecate them (via conf or something) I find :: to be much more readable than cast(). 3. 'Ugly' things like DISTINCT ON expression [, ...] (see http://chernowiki.ru/index.php?node=38#A13) The alternatives to distinct on are painful. They are generally both harder to read and run slower. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to tell how much of the database is being used for data.
On Fri, Feb 24, 2006 at 03:04:27PM -0800, Steve Oualline wrote: Question: How can I tell how much free space is in the database itself? contrib/pgstattuple shows dead tuple and free space statistics for individual tables. Will that work? Background: We want to keep as many records as possible in the database as possible. Currently we fill the database until the disk usage reaches 80% then delete and VACUUM FULL the tables. Are you deleting all records or only some? If all then TRUNCATE would be faster than DELETE + VACUUM FULL and it would have the same space-releasing effect, although it can't be used in all cases (see the TRUNCATE documentation for more information). We'd like to just VACUUM but only VACUUM FULL is sure to release disk space to the operating system. However if we knew how much space was free in the database itself, we could judge how many new records we could dump into it. If you intend to insert more data into the tables then what's the purpose of giving space back to the OS when you're just going to be allocating it again? With frequent vacuuming and reasonable FSM settings you should be able to keep the tables around a certain size. Or do you have usage patterns that make that infeasible? If so, what are they? What version of PostgreSQL are you running? How often do you do regular vacuums? -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Operator for int8 array
Dear sir, We are already in the process of migrating toward 8.1 . For existing support, we like to support with 7.1.3 . Along with 7.1.3 release, contrib/array has been given for array iterator support for int4 type array. It is working fine. We changed it similarly, for int8 type array. It is not working and postmaster receives signal 11. We like to know whether is it feasible or not? thanks and regards, stp. On Fri, 24 Feb 2006, Tom Lane wrote: K.Deepa [EMAIL PROTECTED] writes: I need operator for int8 array. I tried changing the code in contrib/ and compiled. When I tried executing the query, it is dumping. Kindly tell me if there is alternative to overcome the problem. I am using postgresql 7.1.3 version. 7.1.3? Egad. Get yourself onto some remotely modern version of PG. 7.1 is nearly five years old and has many known serious bugs, of both data-loss and security flavors. You didn't say exactly what you needed to do with an int8 array, but you may well find that 8.1 can do it already. The array support is far superior now to what it was in 7.1. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Operator for int8 array
On Mon, 27 Feb 2006, S.Thanga Prakash wrote: Dear sir, We are already in the process of migrating toward 8.1 . For existing support, we like to support with 7.1.3 . Along with 7.1.3 release, contrib/array has been given for array iterator support for int4 type array. It is working fine. We changed it similarly, for int8 type array. It is not working and postmaster receives signal 11. We like to know whether is it feasible or not? why not ? thanks and regards, stp. On Fri, 24 Feb 2006, Tom Lane wrote: K.Deepa [EMAIL PROTECTED] writes: I need operator for int8 array. I tried changing the code in contrib/ and compiled. When I tried executing the query, it is dumping. Kindly tell me if there is alternative to overcome the problem. I am using postgresql 7.1.3 version. 7.1.3? Egad. Get yourself onto some remotely modern version of PG. 7.1 is nearly five years old and has many known serious bugs, of both data-loss and security flavors. You didn't say exactly what you needed to do with an int8 array, but you may well find that 8.1 can do it already. The array support is far superior now to what it was in 7.1. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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
