Re: [GENERAL] backup and restore
Anybody show for me ? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426698 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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] Views- Advantages and Disadvantages
On Fri, 11 May 2007 04:24:55 +0200, Tom Lane <[EMAIL PROTECTED]> wrote:
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes:
Would it be reasonable to suggest that later versions of PostgreSQL
could examine if a function changes data, and quietly marks a function
as 'stable' if it doesn't?
My instinctive CS-major reply to that is "only if you've found a
solution to the halting problem". However, it's possible that we could
detect this case for a useful subset of real-world functions ... not
sure offhand what could be covered.
regards, tom lane
Why not simply have PG issue a warning if the user doesn't specify one of
("stable", "immutable", etc) on function creation ?
like :
WARNING: Function marked as Volatile by default
INFO: if the function does not modify the database, you might want to mark
it STABLE or IMMUTABLE to improve performance
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Views- Advantages and Disadvantages
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/10/07 22:21, Tom Lane wrote: > Ron Johnson <[EMAIL PROTECTED]> writes: >> On 05/10/07 21:24, Tom Lane wrote: >>> My instinctive CS-major reply to that is "only if you've found a >>> solution to the halting problem". However, it's possible that we could >>> detect this case for a useful subset of real-world functions ... not >>> sure offhand what could be covered. > >> If there are no INSERT, UPDATE or DELETE statements in the function? > > Nor any function calls ... which leaves about nothing ... I figured that might be the sticky wicket. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQ+wqS9HxQb37XmcRAt25AJ9mt9IkQjCJBV3EySDRyvzE5bcu/wCeOAiv ntHA65FcBMU3dmLsP1ZD4lE= =sbtA -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Views- Advantages and Disadvantages
Ron Johnson <[EMAIL PROTECTED]> writes: > On 05/10/07 21:24, Tom Lane wrote: >> "Leif B. Kristensen" <[EMAIL PROTECTED]> writes: >>> Would it be reasonable to suggest that later versions of PostgreSQL >>> could examine if a function changes data, and quietly marks a function >>> as 'stable' if it doesn't? >> >> My instinctive CS-major reply to that is "only if you've found a >> solution to the halting problem". However, it's possible that we could >> detect this case for a useful subset of real-world functions ... not >> sure offhand what could be covered. > > If there are no INSERT, UPDATE or DELETE statements in the function? And all functions called from inside the one being run as well (recursive condition, of course)... -- Jorge Godoy <[EMAIL PROTECTED]> pgpijw7CEq76Z.pgp Description: PGP signature
Re: [GENERAL] Views- Advantages and Disadvantages
Ron Johnson <[EMAIL PROTECTED]> writes: > On 05/10/07 21:24, Tom Lane wrote: >> My instinctive CS-major reply to that is "only if you've found a >> solution to the halting problem". However, it's possible that we could >> detect this case for a useful subset of real-world functions ... not >> sure offhand what could be covered. > If there are no INSERT, UPDATE or DELETE statements in the function? Nor any function calls ... which leaves about nothing ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/10/07 20:43, John Gateley wrote: > Sorry if this is a FAQ, I did search and couldn't find much. > > I need to make my Postgresql installation fault tolerant. > I was imagining a RAIDed disk array that is accessible from two > (or multiple) computers, with a postmaster running on each computer. > (Hardware upgrades could then be done to each computer at different > times without losing access to the database). > > Is this possible? > > Is there another way to do this I should be looking at? PostgreSQL does not have a Distributed Lock Manager, so the two postmasters could not coordinate locking and updating. *Maybe* it would work if you put your data on to of OCFS2 filesystems, but I doubt it. Of course, you could always run OpenVMS. You can get *big*, used Alphas for a song. The yearly software licensing fees would be pretty steep, though. http://en.wikipedia.org/wiki/VMScluster http://en.wikipedia.org/wiki/Distributed_lock_manager - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQ9u5S9HxQb37XmcRAhyyAKCWghW9kN+yttTndbRmvvTJY9n0vQCfdt60 C/oVMevsTtMt6SGCBSWZHAU= =hesp -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Views- Advantages and Disadvantages
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/10/07 21:24, Tom Lane wrote: > "Leif B. Kristensen" <[EMAIL PROTECTED]> writes: >> Would it be reasonable to suggest that later versions of PostgreSQL >> could examine if a function changes data, and quietly marks a function >> as 'stable' if it doesn't? > > My instinctive CS-major reply to that is "only if you've found a > solution to the halting problem". However, it's possible that we could > detect this case for a useful subset of real-world functions ... not > sure offhand what could be covered. If there are no INSERT, UPDATE or DELETE statements in the function? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGQ9nCS9HxQb37XmcRAuxyAJ9Setk7j5/xg5jwvNi3o6RDceuGLACg1FDS LptxOrJvoNVSjEATWIeFo+Y= =5MT8 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Missing magic block
Mario Munda <[EMAIL PROTECTED]> writes: > I had to comment some includes out, or else i get some errors. Perhaps you are compiling against an old or incomplete set of Postgres header files? > #ifdef PG_MODULE_MAGIC > PG_MODULE_MAGIC; > #endif The problem with that coding is that it will silently not produce a magic block if you are compiling against pre-8.2 Postgres headers. If you remove the #ifdef protection does it still compile? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] WAL file internals and why a 64 bit will not work on a 32 bit
"Dhaval Shah" <[EMAIL PROTECTED]> writes: > If I partition my disk differently between the primary and standby > will that be a problem? Only if the slave runs out of space in a place where the master doesn't. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Installation fails on windows vista
It wasn't a vista failing or complication - I should have used the machine name for Account name, not localhost...sorry for cluttering up the list. -- View this message in context: http://www.nabble.com/Installation-fails-on-windows-vista-tf3724831.html#a10424989 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Views- Advantages and Disadvantages
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > Would it be reasonable to suggest that later versions of PostgreSQL > could examine if a function changes data, and quietly marks a function > as 'stable' if it doesn't? My instinctive CS-major reply to that is "only if you've found a solution to the halting problem". However, it's possible that we could detect this case for a useful subset of real-world functions ... not sure offhand what could be covered. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Views- Advantages and Disadvantages
On Thu, 10 May 2007 00:06:06 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > ... I suspect the > important point here is that if you have > > CREATE VIEW v AS SELECT sis, boom, bah ... > > then > > SELECT ... FROM ..., v, ... > > will be rewritten to the same parsetree as if you'd written > > SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ... > > and then everything hinges on what the planner is able to do with that. > In simple cases the planner is able to "flatten" the sub-SELECT together > with the outer query and you get a reasonable plan, but if it fails to > do that then you might get a pretty bad plan. I think some people might > complain that "views are slow" because they compared the view to a case > that is not exactly the above mechanical transformation, but one where > they had applied some simplification/optimization that was obvious to > them but not to the planner. I think I have a classic example of this (for older pg versions anyway) - we have a lot of views with a left join in them and performance is awful when the view is inner joined to another table. "select v.* from v where key_of_1st_table = blah" takes a small fraction of a second. "select v.* from v join analysed_tmp_containing_only_blah using (key_of_1st_table)" takes a coffee and a doughnut. The outer join reordering in 8.2 should solve this situation though? klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Fault Tolerant Postgresql (two machines, two postmasters, one disk array)
Sorry if this is a FAQ, I did search and couldn't find much. I need to make my Postgresql installation fault tolerant. I was imagining a RAIDed disk array that is accessible from two (or multiple) computers, with a postmaster running on each computer. (Hardware upgrades could then be done to each computer at different times without losing access to the database). Is this possible? Is there another way to do this I should be looking at? Thanks, j ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] schema of system tables
On May 10, 2007, at 8:19 , Christian Rolle wrote: does somebody know a link to pdf or whatever displaying the schema of system-tables? or better has somebody something like this? Have you checked the extensive PostgreSQL documentation? Chapter 43. System Catalogs http://www.postgresql.org/docs/8.2/interactive/catalogs.html Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] schema of system tables
Christian Rolle wrote: > hello guys, > > does somebody know a link to pdf or whatever displaying the schema of > system-tables? > or better has somebody something like this? There is an old presentation by Bruce Momjian about it. http://www.postgresql.org/files/developer/internalpics.pdf page 64 It's a bit outdated but it is good enough for an introduction. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Issue with database Postgresql :(
how can I set the client_encoding to what I need? ---(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] table change
Is there a way of asking Postgresql if a particular table was changed without going back through the logs - like a last modified flag or even just a changed flag? -- Regards D. Bird
Re: [GENERAL] Missing magic block
"Brad Buran" je napisal:
> Hi Martijn,
>
> Thank you very much for the suggestion:
>
> > > CREATE FUNCTION add_one(IN int)
> > > RETURNS int
> > > AS 'add_one'
> > > LANGUAGE C;
>
> I corrected this to say:
>
> AS 'Project1', 'add_one'
>
> And restarted psql (rebooted for that matter as well) and am still getting
> the same error.
>
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
The same problem with me.
-- test_func.c
#include
//#include
//#include
#include
//#include
#include
#include
#include
#include "pgmagic.h"
PG_FUNCTION_INFO_V1(plsample_call_handler);
Datum plsample_call_handler(PG_FUNCTION_ARGS)
{
Datum retval;
// retval = ...
return retval;
}
I had to comment some includes out, or else i get some errors.
-- pgmagic.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
-- this is my makefile
all: gen_code.c
g++ -fpic -I/usr/local/include -I/usr/local/include/postgresql/
server/ -c test_func.c
g++ -shared -o test_func.so test_func.o
This is what psql returns (phppgadmin):
SQL error:
ERROR: incompatible library "/home/mario/tests/psql_c_func/
test_func.so": missing magic block
HINT: Extension libraries are required to use the PG_MODULE_MAGIC
macro.
In statement:
CREATE FUNCTION "plsample_call_handler" () RETURNS void AS '/home/
mario/tests/psql_c_func/test_func.so','plsample_call_handler' LANGUAGE
"C"
I have allready lost four hours for this. What is the problem??
P.S.:
select version() returns:
PostgreSQL 8.2.0 on i386-unknown-freebsd6.1, compiled by GCC gcc (GCC)
3.4.4 [FreeBSD] 20050518
Thanks in advance.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Solaris Postgresql 8.1.8 vs Postgresql 8.2.4
On May 9, 4:56 am, [EMAIL PROTECTED] (Simon Smith) wrote: > I am planning to set up a new solaris 10 sparc server with a postgresql > database. > > It looks like solaris 10 comes with version 8.1.8 of postgres. > > Is there any benefit in using the 8.1.8 included solaris version over the > current release. > > The sun site mentions several enhancement to the solaris version. Do these > enhancements outweigh > the features and bug fixes of newer postgres releases. > > Thanks, > Simon > > > No need to miss a message. Get email on-the-go > with Yahoo! Mail for Mobile. Get started.http://mobile.yahoo.com/mail www.blastwave.com has a package for 8.2.3. You might look into that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] schema of system tables
hello guys, does somebody know a link to pdf or whatever displaying the schema of system-tables? or better has somebody something like this? i know this is existing for mssql for example, and why not for postgres? it makes something easier to overview... i would be thankful to get something like this. bless chris Mit freundlichem Gruß - Christian Rolle - -- Büro für praktische Informatik - Kruth & Schröder GbR Gesellschafter: Alexander Kruth, Jörg Schröder Philipp-Müller-Straße 12, 23966 Wismar Tel. 03841/758-1212 Mobil 0160-9107615 Fax 03841/758-1211 begin:vcard fn:Christian Rolle n:Rolle;Christian email;internet:[EMAIL PROTECTED] tel;work:03841/758-1212 tel;cell:0160-9107615 note;quoted-printable:Mit freundlichem Gru=C3=9F=0D=0A= =0D=0A= =0D=0A= =0D=0A= - Christian Rolle -=0D=0A= =0D=0A= =0D=0A= =0D=0A= -- = =0D=0A= =0D=0A= B=C3=BCro f=C3=BCr praktische Informatik - Kruth & Schr=C3=B6der GbR=0D=0A= =0D=0A= Gesellschafter: Alexander Kruth, J=C3=B6rg Schr=C3=B6der=0D=0A= =0D=0A= Philipp-M=C3=BCller-Stra=C3=9Fe 12, 23966 Wismar=0D=0A= =0D=0A= Tel. 03841/758-1212 Mobil 0160-9107615 Fax 03841/758-1211 version:2.1 end:vcard ---(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] are foreign keys realized as indexes?
Felix Kater wrote: I am not bound to indexes, however, wonder if foreign keys itself are non-atomic functionality. I mean: if foreign keys are based on some other lower level functionality like indexes or anything else which I could use as a substitute--in what way ever. Of course, I want to gain the same (referential integrity etc.). If foreign keys are, however, something unique which can't be replaced by any other pg function (I am of course not taking into account things like multiple queries bound together by transactions...) then I have to go though it and implement it into my pg interface (looking at the information_schema: This seems to be quite a bunch of work...). Semantics are not a trivial thing. Foreign keys are a fundamental semantic of the relational model. They do not mean the same thing as an index at all. I find it strange that anyone would resist the notions of primary and foreign keys, when they are the basis of the relational model. Indexes aren't even part of the relational model - they are a hack to enhance performance. Sure they ultimately break down to machine instructions, but that's in a whole different domain of discourse. A data model is built up from primary keys, foreign keys and dependent data. They are fundamental. They /are/ the building blocks of your database. Expressing these molecular concepts in terms of their constituent atoms will not convey the molecular properties; you lose a tremendous amount of information. Just use the syntax that best expresses your structure: PRIMARY KEY and FOREIGN KEY. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Views- Advantages and Disadvantages
Michael Glaesemann <[EMAIL PROTECTED]> writes: > Two people now have stated without much qualification that views have > some kind of associated performance (Brent Woods) or optimization > (Dann Corbit) penalty. Where does this idea come from? Views in > PostgreSQL are just rewritten with the view query inlined! There's > not much overhead there AIUI. Well, it takes some cycles to rewrite the query with the inserted sub-select, but probably fewer than would be taken to parse and analyze the query if it had been written out longhand (the stored form of the view has already gone through parse analysis, so we don't have to repeat that work for it). AFAIK that's at worst a wash. I suspect the important point here is that if you have CREATE VIEW v AS SELECT sis, boom, bah ... then SELECT ... FROM ..., v, ... will be rewritten to the same parsetree as if you'd written SELECT ... FROM ..., (SELECT sis, boom, bah ...) AS v, ... and then everything hinges on what the planner is able to do with that. In simple cases the planner is able to "flatten" the sub-SELECT together with the outer query and you get a reasonable plan, but if it fails to do that then you might get a pretty bad plan. I think some people might complain that "views are slow" because they compared the view to a case that is not exactly the above mechanical transformation, but one where they had applied some simplification/optimization that was obvious to them but not to the planner. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Dangers of fsync = off
Thanks, Bill and Scott, for your responses. To summarize, turning fsync off on the master of a Slony-I cluster is probably safe if you observe the following: 1. When failover occurs, drop all databases on the failed machine and sync it with the new master before re-introducing it into the cluster. Note that the failed machine must not be returned to use until this is done. 2. Be aware that the above implies that you will lose any transactions which did not reach the standby machine prior to failure, violating the Durability component of ACID. This is true of any system which relies on asynchronous replication and automatic failover. - Joel ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Installation fails on windows vista
Note I'm accepting all other defaults for the postgres installation, cluster etc. There is no existing postgres install on the machine, though there was before. I have noticed that when the postgres user is created by the installer, it's not a member of any group. Even if I add it to the admins group, postgres will not recognize use that account for the installation, because were I spec localhost for domain, the posgres acct is regarded as belonging to the workgroup. -- View this message in context: http://www.nabble.com/Installation-fails-on-windows-vista-tf3724831.html#a10424270 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Windows installation; why is PL/python shown but disabled?
For some reason a fresh install of python made a difference...now at least the plpython checkbox is enabled. But per my other post today I can't get it to install on vista at all now. -- View this message in context: http://www.nabble.com/Windows-installation--why-is-PL-python-shown-but-disabled--tf3724331.html#a10424148 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Installation fails on windows vista
I needed to reinstall postgres on a box and so far have had no success. I have UAC disabled. I enter localhost, password, ask it to create the postgres user when prompted. The user I'm installing as has admin rights on the notebook (same user as postgres was originally installed under). I get various errors that all result in failure. If the postgres user remains from a previous install, it fails with 'user account exists' (this is weird because during the prev dialog is said the user didn't exist, should it be created). If the postgres user does not exist, the installer creates the user. But then I get an error "internal account lookup failure. no mapping between account names and security ids was done". This is windows vista ultimate and postgres 8.2.4-1 -- View this message in context: http://www.nabble.com/Installation-fails-on-windows-vista-tf3724831.html#a10423933 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] WAL file internals and why a 64 bit will not work on a 32 bit
Thanks. If I partition my disk differently between the primary and standby will that be a problem? Regards Dhaval On 5/10/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Dhaval Shah wrote: > I do know that WAL files taken from a 64 bit OS will not work on a 32 > bit OS. However I have to prepare a technical answer to this. > > That is, questions like - why a WAL file from 64 bit will not work in > 32 bit. Also does the WAL file differ for same architecture but > different kind of partitions? The WAL files track on-disk changes. That is, they represent the bytes changed in individual blocks. So - both machines will need to have *identical* on-disk formats for the WAL transfer to work. It can be something as small as a configuration option chosen when compiling PostgreSQL. For example - you can change between floating-point and integer date-times at ./configure time and if you use different settings on two identical machines then the WAL files will be incompatible. The obvious incompatibility I'd expect in a 32 to 64-bit changeover would be alignment of data fields to 32 or 64-bit boundaries. I've not checked, but I'd be surprised if there wasn't some difference there. -- Richard Huxton Archonet Ltd -- Dhaval Shah ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tokenize string for tsearch?
Magnus Hagander wrote:
> On Mon, May 07, 2007 at 05:31:02PM -0700, Ottavio Campana wrote:
>> Hi, I'm trying to use tsearch2 for the first time and I'm having a
>> problem setting up a query
>>
>> If I execute
>>
>> SELECT * from test_table where ts_desc @@ to_tsquery ('hello&world');
>>
>> it works, but I'm having the problem that the string used for the query
>> is not 'hello&world' but 'hello world', Moreover, it can have an
>> arbitrary number of spaces between the words, so I cannot just
>> substitute the spaces with &, because 'hello&&world' gives error.
>>
>> What is the safest way transform a string into a list of words "anded"
>> together?
>
> Look at plainto_tsquery().
db=# SELECT plainto_tsquery('default', 'hello word');
ERROR: function plainto_tsquery("unknown", "unknown") does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
I'm using 8.1.8 and I don't find plainto_tsquery in tsearch2.sql
What can I do?
Thank you.
--
Non c'e' piu' forza nella normalita', c'e' solo monotonia.
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Pattern Matching - Range of Letters
William Garrison wrote:
That won't work if you have a value "Anz" in there. It would be in
the gap between An and Am.
Yes, I realized that too. My solution to it is a bit of a hack, but it's
easy and it works for me in this case. I translate everything to
uppercase and simply append 'ZZ' to the end of the second string.
None of the strings I am comparing to are longer than 6 characters, and
there are no numerical values in them.
Ron
create table test (test text);
insert into test values ('A');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
insert into test values ('e');
insert into test values ('Ab');
insert into test values ('Ac');
insert into test values ('Amz');
insert into test values ('Az');
select * from test where test between 'A' and 'Am';
"A"
"Ab"
"Ac"
select * from test where test between 'An' and 'Bc';
"Az"
I wouldn't use between in this case. I'd suggest this:
select * from test where test >= 'A' and test <'Am';
"A"
"Ab"
"Ac"
select * from test where test >= 'Am' and test <'Bc';
"Amz"
"Az"
The end will be tricky because "" is not < "zz" so you will need
the last select to be
select * from test where test >= 'Yi';
The beginning will be tricky too if you allow things that come before
A such as 0-9 or spaces.
Richard Broersma Jr wrote:
--- Ron St-Pierre <[EMAIL PROTECTED]> wrote:
I'm sure that others have solved this but I can't find anything with
my (google and archive) searches. I need to retrieve data where the
text field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
Yi-Zz
Does anyone know of a good approach to achieve this? Should I be
looking into regular expressions, or maybe converting them to their
ascii value first?
Regular expressions would work, but a between statement should work
also.
SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] xml to db converter
Does anyone know of a tool that will generate a postgres database schema given an xml schema(xsd). Have tried xmlspy which says it does so but it only has limited postgres support and then it crashes. regards Garry ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Pattern Matching - Range of Letters
That won't work if you have a value "Anz" in there. It would be in the
gap between An and Am.
create table test (test text);
insert into test values ('A');
insert into test values ('b');
insert into test values ('c');
insert into test values ('d');
insert into test values ('e');
insert into test values ('Ab');
insert into test values ('Ac');
insert into test values ('Amz');
insert into test values ('Az');
select * from test where test between 'A' and 'Am';
"A"
"Ab"
"Ac"
select * from test where test between 'An' and 'Bc';
"Az"
I wouldn't use between in this case. I'd suggest this:
select * from test where test >= 'A' and test <'Am';
"A"
"Ab"
"Ac"
select * from test where test >= 'Am' and test <'Bc';
"Amz"
"Az"
The end will be tricky because "" is not < "zz" so you will need the
last select to be
select * from test where test >= 'Yi';
The beginning will be tricky too if you allow things that come before A
such as 0-9 or spaces.
Richard Broersma Jr wrote:
--- Ron St-Pierre <[EMAIL PROTECTED]> wrote:
I'm sure that others have solved this but I can't find anything with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
Yi-Zz
Does anyone know of a good approach to achieve this? Should I be looking
into regular expressions, or maybe converting them to their ascii value
first?
Regular expressions would work, but a between statement should work also.
SELECT *
FROM Your_table AS YT
WHERE YT.text_field BETWEEN 'Aa' AND 'An';
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Windows installation; why is PL/python shown but disabled?
I have python 2.4 installed; python24\lib in the path; why is the PL\python option presented but disabled in 8.2 postgres installer? There must be some conditions under which it (and the other langs) are enabled. The only one of the 7 show that is enabled is PL/pgsql. I've found this to be true for both windows xp and vista. -- View this message in context: http://www.nabble.com/Windows-installation--why-is-PL-python-shown-but-disabled--tf3724331.html#a10422210 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Views- Advantages and Disadvantages
On Thursday 10. May 2007 21:21, Tom Lane wrote: >"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: >> I haven't pondered the subtleties of 'stable', 'immutable' or >> 'volatile' yet, but rather reckoned that the default would do. > >Yeah, I was against this particular change actually, because I > expected that it would cause more problems for people who hadn't paid > close attention to this point than it'd fix for those trying to do > cute things. > >> Here are the function definitions: >AFAICS you ought to mark both of those STABLE, since they use but > don't change database data. Tom, thanks for your explanation. As always, it is lucid and to the point. Would it be reasonable to suggest that later versions of PostgreSQL could examine if a function changes data, and quietly marks a function as 'stable' if it doesn't? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Pattern Matching - Range of Letters
Thanks Richard and Joshua, I had no idea that BETWEEN worked for text. SELECT * FROM Your_table AS YT WHERE YT.text_field BETWEEN 'Aa' AND 'An'; postgres=# select * from test where test between 'A' and 'An'; test -- A Ab Ac (3 rows) Ron Ron St-Pierre wrote: I'm sure that others have solved this but I can't find anything with my (google and archive) searches. I need to retrieve data where the text field is within a certain range e.g. A-An Am-Bc Bc-Eg Yi-Zz Does anyone know of a good approach to achieve this? Should I be looking into regular expressions, or maybe converting them to their ascii value first? Any comments are appreciated. postgres 8.2.4, RHEL Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Pattern Matching - Range of Letters
Richard Broersma Jr wrote: --- Ron St-Pierre <[EMAIL PROTECTED]> wrote: I'm sure that others have solved this but I can't find anything with my (google and archive) searches. I need to retrieve data where the text field is within a certain range e.g. A-An Am-Bc Bc-Eg Regular expressions would work, but a between statement should work also. SELECT * FROM Your_table AS YT WHERE YT.text_field BETWEEN 'Aa' AND 'An'; Ron, in case it's not clear, if an index on text_field exists, the planner can use it to make such queries run relatively fast. - John D. Burger MITRE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Pattern Matching - Range of Letters
Ron St-Pierre wrote:
I'm sure that others have solved this but I can't find anything with my
(google and archive) searches. I need to retrieve data where the text
field is within a certain range e.g.
A-An
Am-Bc
Bc-Eg
Yi-Zz
Does anyone know of a good approach to achieve this? Should I be looking
into regular expressions, or maybe converting them to their ascii value
first?
postgres=# create table test (test text);
CREATE TABLE
postgres=# insert into test values ('A');
INSERT 0 1
postgres=# insert into test values ('b');
INSERT 0 1
postgres=# insert into test values ('c');
INSERT 0 1
postgres=# insert into test values ('d');
INSERT 0 1
postgres=# insert into test values ('e');
INSERT 0 1
postgres=# insert into test values ('Ab');
INSERT 0 1
postgres=# insert into test values ('Ac');
INSERT 0 1
postgres=# insert into test values ('Az');
INSERT 0 1
postgres=# select * from test where test between 'A' and 'An';
test
--
A
Ab
Ac
(3 rows)
Any comments are appreciated.
postgres 8.2.4, RHEL
Thanks
Ron St.Pierre
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Pattern Matching - Range of Letters
--- Ron St-Pierre <[EMAIL PROTECTED]> wrote: > I'm sure that others have solved this but I can't find anything with my > (google and archive) searches. I need to retrieve data where the text > field is within a certain range e.g. > A-An > Am-Bc > Bc-Eg > > Yi-Zz > > Does anyone know of a good approach to achieve this? Should I be looking > into regular expressions, or maybe converting them to their ascii value > first? Regular expressions would work, but a between statement should work also. SELECT * FROM Your_table AS YT WHERE YT.text_field BETWEEN 'Aa' AND 'An'; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Pattern Matching - Range of Letters
I'm sure that others have solved this but I can't find anything with my (google and archive) searches. I need to retrieve data where the text field is within a certain range e.g. A-An Am-Bc Bc-Eg Yi-Zz Does anyone know of a good approach to achieve this? Should I be looking into regular expressions, or maybe converting them to their ascii value first? Any comments are appreciated. postgres 8.2.4, RHEL Thanks Ron St.Pierre ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Views- Advantages and Disadvantages
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > On Thursday 10. May 2007 19:23, Tom Lane wrote: >> Are get_parent() and/or get_pbdate() marked volatile by any chance? >> 8.2 is more conservative about optimizing sub-selects involving >> volatile functions than previous releases were, because we got >> complaints about surprising behavior when a volatile function is >> executed more or fewer times than the text of the query would >> suggest. > I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile' > yet, but rather reckoned that the default would do. Yeah, I was against this particular change actually, because I expected that it would cause more problems for people who hadn't paid close attention to this point than it'd fix for those trying to do cute things. > Here are the function definitions: > CREATE OR REPLACE FUNCTION get_parent(INTEGER,INTEGER) RETURNS INTEGER > AS $$ > DECLARE > person ALIAS FOR $1;-- person ID > rel_type ALIAS FOR $2; -- gender code (1=male, 2=female) > par INTEGER;-- person ID of parent, returned by func > BEGIN > SELECT parent_fk INTO par FROM relations > WHERE child_fk = person AND relation_type = rel_type; > RETURN COALESCE(par,0); -- will return parent ID if it exists, 0 > otherwise > END; > $$ LANGUAGE plpgsql; > CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$ > DECLARE > pb_date TEXT; > BEGIN > SELECT event_date INTO pb_date FROM events, participants > WHERE events.event_id = participants.event_fk > AND participants.person_fk = $1 > AND events.tag_fk IN (2,62,1035) > AND participants.is_principal IS TRUE; > RETURN COALESCE(pb_date,'31'); > END; > $$ LANGUAGE plpgsql; AFAICS you ought to mark both of those STABLE, since they use but don't change database data. 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
Re: [GENERAL] Views- Advantages and Disadvantages
On Thursday 10. May 2007 19:23, Tom Lane wrote: >"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: >> CREATE OR REPLACE VIEW tmg_persons AS >> SELECT >> person_id, >> get_parent(person_id,1) AS father_id, >> get_parent(person_id,2) AS mother_id, >> last_edit, >> get_pbdate(person_id) AS pb_date, >> get_pddate(person_id) AS pd_date, >> gender AS s, >> living AS l, >> is_public AS p >> FROM persons; > >Are get_parent() and/or get_pbdate() marked volatile by any chance? >8.2 is more conservative about optimizing sub-selects involving > volatile functions than previous releases were, because we got > complaints about surprising behavior when a volatile function is > executed more or fewer times than the text of the query would > suggest. If they are really stable or immutable, marking them so > would probably help here. (If they fetch from another table, stable > is the right marking.) Tom, I haven't pondered the subtleties of 'stable', 'immutable' or 'volatile' yet, but rather reckoned that the default would do. Here are the function definitions: CREATE OR REPLACE FUNCTION get_parent(INTEGER,INTEGER) RETURNS INTEGER AS $$ DECLARE person ALIAS FOR $1;-- person ID rel_type ALIAS FOR $2; -- gender code (1=male, 2=female) par INTEGER;-- person ID of parent, returned by func BEGIN SELECT parent_fk INTO par FROM relations WHERE child_fk = person AND relation_type = rel_type; RETURN COALESCE(par,0); -- will return parent ID if it exists, 0 otherwise END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_pbdate(INTEGER) RETURNS TEXT AS $$ DECLARE pb_date TEXT; BEGIN SELECT event_date INTO pb_date FROM events, participants WHERE events.event_id = participants.event_fk AND participants.person_fk = $1 AND events.tag_fk IN (2,62,1035) AND participants.is_principal IS TRUE; RETURN COALESCE(pb_date,'31'); END; $$ LANGUAGE plpgsql; 'relations', 'events', and 'participants' are actual tables. So, what do you recommend? -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(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] Views- Advantages and Disadvantages
"Leif B. Kristensen" <[EMAIL PROTECTED]> writes: > [ this query got slow in 8.2: ] > $query = "select person_id, pb_date from tmg_persons > where father_id = $p or mother_id = $p > order by pb_date"; > tmg_persons is a view involving several function calls, and is a legacy > from an earlier, flatter data model where the 'persons' table actually > had this structure. I'm still using it in my Web application, and the > primary function of the view is to make an easy export: > CREATE OR REPLACE VIEW tmg_persons AS > SELECT > person_id, > get_parent(person_id,1) AS father_id, > get_parent(person_id,2) AS mother_id, > last_edit, > get_pbdate(person_id) AS pb_date, > get_pddate(person_id) AS pd_date, > gender AS s, > living AS l, > is_public AS p > FROM persons; Are get_parent() and/or get_pbdate() marked volatile by any chance? 8.2 is more conservative about optimizing sub-selects involving volatile functions than previous releases were, because we got complaints about surprising behavior when a volatile function is executed more or fewer times than the text of the query would suggest. If they are really stable or immutable, marking them so would probably help here. (If they fetch from another table, stable is the right marking.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Replication for PG 8 recommendations
Hannes Dorbath wrote: Replicate the whole block device, PostgreSQL sits on. For Linux, are you talking about something like DRDB? That would be nice in that it would also replicate the web app itself. David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Views- Advantages and Disadvantages
Ashish Karalkar wrote: > Hello All, > > Can anybody please point me to Advantages and Disadvantages of using view > > > With Regards > Ashish... Well, IMHO views are part of the "business logic" and not of the data model. You can also think of them as an API to access the data from applications (clients). By defining some nice views, you allow writing a client with little knowledge about the actual database design. And clients written by different people access the data consistently. However, this is a two-edged sword. An API is usually designed to be generic enough. One day you may find you just need only part of the funtionality, and you that could do that part more efficently. That's expecially true if the API is used to hide the details away from you. Normal clients may be given access only to the views and not to the actual tables. That's pretty an good design principle, but again it cuts both ways. Think of a database with a "books" table and a "authors" table, with a nice view that joins them. One day you are writing a client application and want to fetch just the list of book ids. Yes, you can select one column from the view, but why execute the join when you don't need it? But if you're given access only to the view, you can't do much about it. Of course this is not specific to views, it's true for any abstraction layer in any context. .TM. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] how to convert a string array to a string. fct array_to_string seem to work only for INT array??
On Thursday, May 10, 2007 6:07 AM David Gagnon wrote
I have a string array(Compte[]) and I need to create the following string
statement to populate a temporary table
statement := ' INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = ' || quote_literal(companyId) || '
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {' || array_to_string(Compte, ',') || '}';
EXECUTE statement;
For now I get :
INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = 'M'
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {cpt1, cpt2}
But I want:
INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = 'M'
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {'cpt1', 'cpt2'}
How can I do that. I expected to find a standard function in the doc to
do that ...
One way is to include the ' in with the delimiter, and start and end the
string with ' as:
AND CSGLNUM IN {' || '\'' || array_to_string(Compte, '\', \'') || '\''
|| '}';
Regards,
George
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Views- Advantages and Disadvantages
On Wednesday 9. May 2007 06:32, Ashish Karalkar wrote: >Hello All, > >Can anybody please point me to Advantages and Disadvantages of using > view Sometimes, a view can fool you into writing hideously expensive queries just because it is the first method that comes to mind. I upgraded to version 8.2.4 a few days ago, and haven't gotten around to change the memory settings. Thus, I just discovered that my pedigree drawing script seemed to hang forever. I finally let it run its course, and it clocked in on about two minutes. In a db where the largest table is about 50,000 rows, this is ridiculous. I opened the script and attacked the first query I found. It will find the children of the focus person and list them ordered by birth date: $query = "select person_id, pb_date from tmg_persons where father_id = $p or mother_id = $p order by pb_date"; tmg_persons is a view involving several function calls, and is a legacy from an earlier, flatter data model where the 'persons' table actually had this structure. I'm still using it in my Web application, and the primary function of the view is to make an easy export: CREATE OR REPLACE VIEW tmg_persons AS SELECT person_id, get_parent(person_id,1) AS father_id, get_parent(person_id,2) AS mother_id, last_edit, get_pbdate(person_id) AS pb_date, get_pddate(person_id) AS pd_date, gender AS s, living AS l, is_public AS p FROM persons; I ran an "explain select" on the query: pgslekt=> explain select person_id, pb_date from tmg_persons where father_id=1130; QUERY PLAN Subquery Scan tmg_persons (cost=0.00..729.06 rows=81 width=36) Filter: (father_id = 1130) -> Seq Scan on persons (cost=0.00..525.96 rows=16248 width=19) (3 rows) Sequential scans usually spell Big Trouble. So, I rewrote the query to read directly from the 'relations' table: $query = "select child_fk, get_pbdate(child_fk) as pb_date from relations where parent_fk = $p order by pb_date"; pgslekt=> explain select child_fk, get_pbdate(child_fk) as pb_date from relations where parent_fk=1130 order by pb_date; QUERY PLAN - Sort (cost=150.52..150.81 rows=117 width=4) Sort Key: get_pbdate(child_fk) -> Bitmap Heap Scan on relations (cost=5.16..146.50 rows=117 width=4) Recheck Cond: (parent_fk = 1130) -> Bitmap Index Scan on parent_key (cost=0.00..5.13 rows=117 width=0) Index Cond: (parent_fk = 1130) (6 rows) And that was it. The script now runs in about 1/10 of a second. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE My Jazz Jukebox: http://www.last.fm/user/leifbk/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] how to convert a string array to a string. fct array_to_string seem to work only for INT array??
Hi all,
I'm messing with this, I think simple, problem. I searched the doc
and the web without success .. hum
I have a string array(Compte[]) and I need to create the following
string statement to populate a temporary table
statement := ' INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = ' || quote_literal(companyId) || '
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {' || array_to_string(Compte, ',') || '}';
EXECUTE statement;
For now I get :
INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = 'M'
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {cpt1, cpt2}
But I want:
INSERT INTO T_CR1 ( CRNUM, CRMONT, CSGLNUM, CRDATE)
SELECT CRNUM, CSGLNUM, CRMONT, CRDATE
FROM CR INNER JOIN CS ON CR.CRNUM = CS.CSCRNUM AND
CR.CRYPNUM = CS.CSYPNUM
WHERE CRYPNUM = 'M'
AND CRDATE <= CURRENT_DATE
AND CSGLNUM IN {'cpt1', 'cpt2'}
How can I do that. I expected to find a standard function in the doc to
do that ...
Thanks for your help!
Best Regards
David
---(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] Invoke trigger after commit
Use a Listen/Notify daemon application and put the Notify on the last line of the transaction. If it does the notify that means that the transaction was completed successfully. Sim Jan Strube wrote: Hi, is there a way to invoke a trigger only if the current transaction is committed? The problem is that my trigger does some kind of logging outside the database and therefore must not be invoked if the transaction is rolled back. Thanks in advance Jan ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Invoke trigger after commit
On 5/10/07, Jan Strube <[EMAIL PROTECTED]> wrote: is there a way to invoke a trigger only if the current transaction is committed? The problem is that my trigger does some kind of logging outside the database and therefore must not be invoked if the transaction is rolled back. PostgreSQL does not implement a kind of "on commit" trigger, but you can simulate them using "notify" and "listen", which are transactional: http://www.postgresql.org/docs/8.2/interactive/sql-notify.html Alexander. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Invoke trigger after commit
Hi, is there a way to invoke a trigger only if the current transaction is committed? The problem is that my trigger does some kind of logging outside the database and therefore must not be invoked if the transaction is rolled back. Thanks in advance Jan ---(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] Dangers of fsync = off
On 5/8/07, Joel Dice <[EMAIL PROTECTED]> wrote: On Tue, 8 May 2007, Andrew Sullivan wrote: > My real question is why you want to turn it off. If you're using a > battery-backed cache on your disk controller, then fsync ought to be > pretty close to free. Are you sure that turning it off will deliver > the benefit you think it will? You may very well be right. I tend to think in terms of software solutions, but a hardware solution may be most appropriate here. In any case, I'm not at all sure this will bring a significant peformance improvement. I just want to understand the implications before I start fiddling; if fsync=off is dangerous, it doesn't matter what the performance benefits may be. Well, fsync=off makes failures harder to cope with. Normally when your operating system crashes/power fails your master server should start up cleanly. If it doesn't -- you've got slave. Now, with fsync=off you should promote slave to master whenever you experience crash/power failure, just to be safe. Having battery backed unit may be cheaper than cost of failovers (time of DBA costs money, downtime also ;)). Do some testing, do some calculations. >> on Y. Thus, database corruption on X is irrelevant since our first step >> is to drop them. > > Not if the corruption introduces problems for replication, which is > indeed possible. That's exactly what I want to understand. How, exactly, is this possible? If the danger of fsync is that it may leave the on-disk state of the database in an inconsistent state after a crash, it would not seem to have any implications for activity occurring prior to the crash. In particular, a trigger-based replication system would seem to be immune. In other words, while there may be ways the master could cause corruption on the slave, I don't see how they could be related to the fsync setting. OK, let's assume you have machine mdb as a master database, and sdb as slave database. mdb has fsync=off and Slony-I is used as a replication system. You have a power failure/system crash/whatever. mdb goes down. Your sdb is consistent, but it's missing, let's say 15 seconds of last transactions which didn't manage to replicate. You don't do failover yet. Your mdb starts up, PostgreSQL replays its Write Ahead Log. Everything seems fine, mdb is up and running, and these 15 seconds of transactions are replicated to sdb. Oops. PostgreSQL seemd to be fine, but since fsync was off, the rows in Money_Transactions weren't flushed to disk (fsync was off), and PostgreSQL thought they should already be on disk (WAL was replayed since last known CHECKPOINT), you didn't actually replicated these transactions. If you are really unlucky you've replicated some old contents of database, and thus now, both your mdb and sdb contain erraneous data. Of course sdb is consistent in terms of "internal structure" but try explaining it to the poor soul who happened to be doing updates on Money_Transactions table. ;-) Of course likelihood of this happening isn't very big -- PostgreSQL really tries to safeguard your data (elephant never forgets ;)), but only as long as you give him a chance. ;) Regards, Dawid ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL file internals and why a 64 bit will not work on a 32 bit
Dhaval Shah wrote: I do know that WAL files taken from a 64 bit OS will not work on a 32 bit OS. However I have to prepare a technical answer to this. That is, questions like - why a WAL file from 64 bit will not work in 32 bit. Also does the WAL file differ for same architecture but different kind of partitions? The WAL files track on-disk changes. That is, they represent the bytes changed in individual blocks. So - both machines will need to have *identical* on-disk formats for the WAL transfer to work. It can be something as small as a configuration option chosen when compiling PostgreSQL. For example - you can change between floating-point and integer date-times at ./configure time and if you use different settings on two identical machines then the WAL files will be incompatible. The obvious incompatibility I'd expect in a 32 to 64-bit changeover would be alignment of data fields to 32 or 64-bit boundaries. I've not checked, but I'd be surprised if there wasn't some difference there. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] In theory question
On 09.05.2007 17:30, Erik Jones wrote: On 09.05.2007 16:13, Naz Gassiep wrote: I think this is close to what MySQL's query cache does. The question is if this should be the job of the DBMS and not another layer. At least the pgmemcache author and I think that it's better done outside the DBMS. See http://people.FreeBSD.org/~seanc/pgmemcache/pgmemcache.pdf for the idea. I just read through that pdf. How does implementing a memcached system with table triggers qualify as outside the database? The point is to have the DBMS _invalidate_ an external Cache, not to fill or use it. Caching in that case should not be done for single SQL statements. You should cache things that have been produced using that query, a rendered part of an HTML page is an example. Think of a news selection on your website, the pages changes when the content of 2-3 tables in your database changes. Here you have the DBMS clear the page from the cache and your application layer re-render it and put the new version in the cache. -- Regards, Hannes Dorbath ---(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] Replication for PG 8 recommendations
On 10.05.2007 06:30, David Wall wrote: On Wed, 2007-05-09 at 14:40 -0700, David Wall wrote: Is there a "preferred" replication system for PG 8 db users? Obviously, we're looking for robustness, ease of operations/installation, low latency and efficient with system and network resources, with an active open source community being preferred. Jeff Davis wrote: http://www.postgresql.org/docs/8.2/static/high-availability.html Thanks. I've seen the options and was hoping for grunt-level realities. Many projects seem to have fallen by the wayside over time. My first impression was towards a Slony-I type solution, but I need large objects and would prefer schema updates to be automatic. I was hoping to hear back on any pitfalls or preferences or "how I'd do it if I could do it again" type stories. We mostly need it for disaster recovery since we're looking to improve upon our current nightly backup/syncs in which we pg_dump the database, SCP it to the backup, then pg_restore on the backup. It's possible WAL copying will do it, too, but don't know if people find this workable or not. Replicate the whole block device, PostgreSQL sits on. -- Regards, Hannes Dorbath ---(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
