Re: [GENERAL] Virtual table with pl/perl?
Alvaro Herrera wrote: Rene Schickbauer escribió: Hi! I'm looking into implementing an updateable "virtual" table using pl/perl. What i basically want to do is read out/update some external resources. For programs using the SQL interface, this should look just like a regular table except that it isn't ACID compliant (no rollbacks). Did you see DBI-Link in pgfoundry.org? Looks interesting. Thanks. Though i'm not yet sure i wanna go this way, i'd still have to implement the database drivers for the stuff i need. Specifically, i need two things: I need to access external devices via snmp and web, as well as the ActiveDirectory for our windows domain controllers. LG Rene -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tuple storage overhead
On Fri, Apr 16, 2010 at 12:40:21PM +0200, Szymon Guz wrote: > I thought that the default fillfactor was much smaller (and haven't checked > that now)... sorry for messing that up. > But let's think of it from the other side: what do you want to do with that > data? Maybe PostgreSQL with it's MVCC's overhead isn't the best solution for > your needs. I'm using this as part of a larger application. The data sets are one aspect of it. The idea is the following: An engineering application generates time-dependent data. One simulation yields a very big text file in tabular format, with hundreds or thousands of columns with output values (often more than Postgres' column limit), one row per timestamp. One such file is generated for each permutation of input values which influence the run of a simulation. This text file is imported into a database so we can perform very quick lookups on the numbers so they can be quickly plotted in a graph. The user can select any number of input permutations and graph the values of any selected output values to view the effect of the variation in input. One can plot any variable against any other, so one join is made for each variable that we want to plot; it joins the timestep values of the variable on the X axis to those on the Y axis. Regards, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Tuple storage overhead
On Fri, Apr 16, 2010 at 11:28:36AM -0400, Merlin Moncure wrote: > If you are storing big data and want to keep the overhead low, the > first thing you need to examine is organizing your data into arrays. > This involves tradeoffs of course and may not work but it's worth a > shot! That does sound interesting. However, I'm storing several hundreds or thousands of data points (depending on the data set). How is Postgresql's overhead when it comes to extracting one or two items from an array in a query? Cheers, Peter -- http://sjamaan.ath.cx -- "The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music." -- Donald Knuth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ltree - how to sort nodes on parent node
On 19 Apr 2010, at 20:26, cojack wrote: >> Alban Hertroys wrote: >> >> It would help if you'd show us what result you expect from ordering the >> above. >> >> Most people would order this by path I think. However that doesn't match >> your sort column and I can't think of any method that would give results >> in such an arbitrary order as you seem to be specifying - unless you set >> it by hand like you do. > Yes, you have right, for example I create new idea of stored data in table: > > here is a paste: http://pastebin.com/4pX5cM7j -- never expired link > > As you can see, I have noodes with numeric type, those nodes present a sort > position by self. And If I type ORDER BY path; I will have data like I want > to have: http://pastebin.com/R4z01LC5 -- never expired link > > Again, you can see now grouped data in his nodes, this is the outputed data > I wanted. If you know better way to make this WITHOUT recursive queries, > give me a hint. Aha, looks like you want to sort each tree level by some user-specified order. You should realise that ltree was contributed before Postgres supported (recursive) CTE's. If you're using ltree in combination with recursive CTE's you're doing twice the work that you need to do - ltree was created as a means to make recursive queries possible in the first place. I think you have basically two ways to go about this: 1). The way you're doing this in your new examples should work, although I'd probably make the ordering numbers part of the category names and split those off when I read them. For example: 27 | 1|Top 28 | 1|Top.1|Science 29 | 1|Top.2|Hobby 30 | 1|Top.3|Colors 31 | 1|Top.1|Science.1|Physics 32 | 1|Top.1|Science.2|Chemistry 33 | 1|Top.1|Science.3|Biology 34 | 1|Top.1|Science.4|History 35 | 1|Top.2|Hobby.1|Fishing 36 | 1|Top.2|Hobby.2|Football 37 | 1|Top.3|Colors.1|Black 38 | 1|Top.3|Colors.2|Red 39 | 1|Top.3|Colors.3|Blue 40 | 1|Top.1|Science.5|Archeology 41 | 1|Top.2|Hobby.3|Swimming 42 | 1|Top.3|Colors.4|Gray 43 | 1|Top.3|Colors.5|Purple 44 | 1|Top.3|Colors.6|Brown 45 | 1|Top.2|Hobby.4|Climbing 2). Drop the ltree column and go with a truly recursive approach, something like this: CREATE TABLE node ( categorytextNOT NULL PRIMARY KEY, sort_order int NOT NULL, parent textREFERENCES tree (category) ON UPDATE CASCADE ON DELETE CASCADE ); WITH RECURSIVE tree AS ( SELECT * FROM node WHERE parent IS NULL UNION ALL SELECT node.* FROM tree, node WHERE node.parent = tree.category ORDER BY sort_order ) SELECT * FROM tree; I haven't actually used recursive CTE's before so there may be some errors in the above, but you get the general idea. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4bcd773910411833268189! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Culturally aware initcap
Hello,
I've devised the following function, that performs the same task as
initcap, but in a "culturally aware fashion", for English. It solves a
common problem I was having with initcap, where the string "ROSEMARY'S
baby DOESN'T LIVE HERE anymore" became "Rosemary'S Baby doesn'T Live
Here Anymore", whereas I wanted to see "Rosemary's Baby Doesn't Live
Here Anymore", while preserving Irish names like O'Shaughnessy and
O'Sullivan. This may be more useful for exclusively English language
databases than the generic initcap().
CREATE OR REPLACE FUNCTION cul_initcap(input_val text) RETURNS text AS
$function_body$
SELECT replace(
replace(
replace(
regexp_replace(initcap($1),
$$'([MST])([^[:upper:][:lower:]]|$)$$,
$$'{...@*#!\1!#*@}\2$$,
'g'
)
, '{...@*#!m!#*@}', 'm')
, '{...@*#!s!#*@}', 's')
, '{...@*#!t!#*@}', 't');
$function_body$
LANGUAGE 'sql' IMMUTABLE;
Now, this works, but is a little inelegant; I couldn't figure out a
better way of having regex_replace's replacement become lower case,
than wrapping part of its output in magical braces of {...@*#! and !...@}
and subsequently replacing those magical braces and their contents
with appropriate, lower-case strings using multiple replace() calls.
One obvious problem with this function is that it will not correctly
initcap a "magical brace enclosed literal", like '{...@*#!t!#*@}' ,
although I dare say that isn't enough of a problem to discourage its
use.
Can someone suggest a better implementation, that doesn't rely on
magical braces? Either way, I'm going to post this on the postgres
wiki under "snippets", because I think it's of general interest, and
it currently lacks a template solution, which it probably should have.
Regards,
Peter Geoghegan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Recognize the creating of duplicate row from trigger
Hi, I have created a trigger which updates the correspondign LogTable for PrimaryTable. If the insert to LogTable would create a duplicate row then the trigger should take no action and should return. Could you tell me how could I achieve this? I have all columns as a primary key in LogTable. It is intentional but I don't want my trigger to fail if tried to insert duplicate row and rollback the transaction. It should not take any action(should not insert duplication row) and returns when duplicate row is recognized. Thanks, Dipti
Re: [GENERAL] Culturally aware initcap
Here's the entry to the wiki: http://wiki.postgresql.org/wiki/Culturally_aware_initcap I've added "you've handling" Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
2010/4/18 Craig Ringer : > Đỗ Ngọc Trí Cường wrote: >> Dear all, >> >> I've a problem but I search all the help file and can't find the solution. >> >> I want to track all action of a specify role on all or one schema in >> database. >> >> Can you help me? > > You can use statement-level logging, though there are no facilities in > statement-level logging to restrict what is logged to only one role's > activity. Wouldn't this work: alter user smarlowe set log_statement = 'all'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote: > Wouldn't this work: > > alter user smarlowe set log_statement = 'all'; IIRC it only works inside the given session (so it needs to be run each time a query will be executed) -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Ltree - how to sort nodes on parent node
> Alban Hertroys wrote: > > > Aha, looks like you want to sort each tree level by some user-specified > order. > > You should realise that ltree was contributed before Postgres supported > (recursive) CTE's. If you're using ltree in combination with recursive > CTE's you're doing twice the work that you need to do - ltree was created > as a means to make recursive queries possible in the first place. > > I think you have basically two ways to go about this: > > 1). The way you're doing this in your new examples should work, although > I'd probably make the ordering numbers part of the category names and > split those off when I read them. For example: > 27 | 1|Top > 28 | 1|Top.1|Science > 29 | 1|Top.2|Hobby > 30 | 1|Top.3|Colors > 31 | 1|Top.1|Science.1|Physics > 32 | 1|Top.1|Science.2|Chemistry > 33 | 1|Top.1|Science.3|Biology > 34 | 1|Top.1|Science.4|History > 35 | 1|Top.2|Hobby.1|Fishing > 36 | 1|Top.2|Hobby.2|Football > 37 | 1|Top.3|Colors.1|Black > 38 | 1|Top.3|Colors.2|Red > 39 | 1|Top.3|Colors.3|Blue > 40 | 1|Top.1|Science.5|Archeology > 41 | 1|Top.2|Hobby.3|Swimming > 42 | 1|Top.3|Colors.4|Gray > 43 | 1|Top.3|Colors.5|Purple > 44 | 1|Top.3|Colors.6|Brown > 45 | 1|Top.2|Hobby.4|Climbing > > > Alban Hertroys > > -- > Screwing up is an excellent way to attach something to the ceiling. > > > !DSPAM:737,4bcd773910411833268189! > > > My and your first example doesn't work fine at all, why? Becouse when we add more thank 10 sub nodes in some node, the 10 node will not be after 9, but after 1 before 2, and this is not good idea to set sort in path. I think the best idea for this will be create other column, with also ltree data type and stored inside a sort/ordering data. Like: 1 1.1 1.1.1 1.1.2 1.1.3 And while selected it from table, just cast it to int. I'll check this and his performance after I return from work. I am not interested about recursive queries, i think this kill ltree idea. -- Regards, cojack. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Int64GetDatum
John R Pierce writes: > Bruce Momjian wrote: >> Ah, good idea. Is pg_config.h the only file that varies from 32 to >> 64-bit? > it *appears* to be here, but I don't have the 64 bit file set to compare > with. there are definately a couple other files that get generated by > ./configure, including ecpg_config.h and another that escapes me at the > moment. ecpg_config.h also has to be stubbed to make this work. The other stuff that could vary (like Makefile.global) gets installed under bin/ or lib/ so if you have a lib-vs-lib64 type of layout then that's safe. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tar error, in pg_start_backup()
Magnus Hagander wrote:
> .
>
> On Fri, Apr 16, 2010 at 11:55 AM, raghavendra t
> wrote:
> > Hi All,
> >
> > For?some setups reason, i started taking Hot backup. In this course I have
> > first issued pg_start_backup('backup') and went to the data directory for
> > backing up in OS format using the command "tar -cf backup.tar? /data" .
> > When i issued this command , tar was generating some errors as show below.
> >
> > bash-3.00# tar -cf 16aprilstandby.tar /db-data/
> > tar: Removing leading `/' from member names
> > tar: /db-data/base/24643/151667: file changed as we read it
> > tar: /db-data/base/24643/151764.2: file changed as we read it
> > tar: /db-data/base/24643/151766: file changed as we read it
> > tar: /db-data/base/24643/151768: file changed as we read it
> > tar: /db-data/base/66412/151969: file changed as we read it
> >
> > After sometime tar has ended and i also issued pg_stop_backup() and i
> > continued the process.
> >
> > My question here is, is this errors generated by tar are to worrisome or
> > whats happening in the backend.
> > Is "tar" file is safewell to use. Could you please tell me.
>
> Those are not errors, they are warnings. As long as you use
> pg_start_backup() and pg_stop_backup() before and after the tar, they
> are perfectly harmless, and can be ignored.
Yes, and the manual does mention this issue:
http://developer.postgresql.org/pgdocs/postgres/continuous-archiving.html#BACKUP-BASE-BACKUP
Some file system backup tools emit warnings or errors if the files they
are trying to copy change while the copy proceeds. When taking a base
backup of an active database, this situation is normal and not an error.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Specific Database Vars
Hi guys, I'm a newbie using PostgreSQL. I know that can use one-row tables but, I'd like to know if it's possible to define variables attached to one specific database, & saveables when the database tables are dumped. Glus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
On Tue, Apr 20, 2010 at 6:11 AM, Devrim GÜNDÜZ wrote: > On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote: >> Wouldn't this work: >> >> alter user smarlowe set log_statement = 'all'; > > IIRC it only works inside the given session (so it needs to be run each > time a query will be executed) Not true: psql show log_statement; log_statement --- none alter user smarlowe set log_statement = 'all'; show log_statement; log_statement --- all \q psql show log_statement; log_statement --- all -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
On Tue, Apr 20, 2010 at 8:31 AM, Scott Marlowe wrote: > On Tue, Apr 20, 2010 at 6:11 AM, Devrim GÜNDÜZ wrote: >> On Tue, 2010-04-20 at 06:03 -0600, Scott Marlowe wrote: >>> Wouldn't this work: >>> >>> alter user smarlowe set log_statement = 'all'; >> >> IIRC it only works inside the given session (so it needs to be run each >> time a query will be executed) > > Not true: > > psql > show log_statement; > log_statement > --- > none > alter user smarlowe set log_statement = 'all'; > show log_statement; > log_statement > --- > all > \q > psql > show log_statement; > log_statement > --- > all Note however that other sessions won't see the change. Only connections that come after the change will see it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert Ecoded values into postrgresql
Tom Lane wrote: > Merlin Moncure writes: > > *) csv is supposed to be CRLF *always*. We do not do this. We do > > however read different types of newlines. So we are a 4180 reader but > > not an emitter. Not so sure if I think changing this is a good idea > > though without exposing a knob. > > Given the lack of field complaints, I think changing this would be a > pretty terrible idea; much more likely to break things than fix them. > It's not like that RFC has huge recognition as The Standard. Should we document we support the standard? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to insert Ecoded values into postrgresql
Bruce Momjian writes: > Tom Lane wrote: >> Given the lack of field complaints, I think changing this would be a >> pretty terrible idea; much more likely to break things than fix them. >> It's not like that RFC has huge recognition as The Standard. > Should we document we support the standard? I'm inclined to think not: there's no evidence that anyone cares about compliance to that RFC, and anyway we *don't* support the standard exactly, per Merlin's report. We'd have to say something like "we support the standard except for line-endings", which is only going to muddy the waters. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anyone use Python/ceODBC/PostgreSQL?
I am trying to get that combination to work, without success. I am running Mac Snow Leopard on my development machine and cannot find anyone who has gotten ceOBDC and libiodbc to work - so I want to ask you guys. Thanks, O. -- Thank you, G.E. Ozz Nixon CEO/Sr. Software Architect 3F, LLC 125 Robin Dr. Barto PA 19504 Office: 1-484-363-2304 Cell: 1-610-698-7976 Email: o...@3flabs.com The information contained in this communication is confidential. It is intended only for the use of the recipients named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are here by notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend the communication to the sender and delete the original message or any copy of it from your computer systems. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection?
how to debug the codes in the PostgresMain() from the begining of this function to the "for (;;)" loop that handles the connection? if i use eclipse cdt to attach the postgres process forked by postmaster, this part of codes from the begining of this PostgresMain() to the "for (;;)" loop could not be debugged. So anybody know how to debug this part codes? thanks. thanks peng
Re: [GENERAL] Ltree - how to sort nodes on parent node
In article <59670b22-30cb-4e6e-83c8-c1d1036c9...@solfertje.student.utwente.nl>, Alban Hertroys writes: > 2). Drop the ltree column and go with a truly recursive approach, something > like this: > CREATE TABLE node ( > categorytextNOT NULL PRIMARY KEY, > sort_order int NOT NULL, > parent textREFERENCES tree (category) > ON UPDATE CASCADE > ON DELETE CASCADE > ); > WITH RECURSIVE tree AS ( > SELECT * > FROM node >WHERE parent IS NULL > UNION ALL > SELECT node.* > FROM tree, node >WHERE node.parent = tree.category >ORDER BY sort_order > ) > SELECT * FROM tree; Here's a working version: WITH RECURSIVE tree (path, category, sort_order, parent) AS ( SELECT category, category, sort_order::text, parent FROM node WHERE parent IS NULL UNION ALL SELECT t.path || '.' || n.category, n.category, t.sort_order || '.' || n.sort_order, n.parent FROM tree t JOIN node n ON n.parent = t.category ) SELECT path FROM tree ORDER BY sort_order -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Specific database vars, again...
Hi again, Maybe, I didn't explain my question enough. I need to record properties that belongs to an specific database (and so, they work at database level... not at global scope: * Could I use the \set statements ? but... the vars defined are not in a database scope but a global, aren't they ?... furthermore, could save these vars when try to dump the database ??? ) * Or, must to create an specific one-row table ?. Glus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ltree - how to sort nodes on parent node
On 20 Apr 2010, at 18:05, Harald Fuchs wrote: > Here's a working version: > > WITH RECURSIVE tree (path, category, sort_order, parent) AS ( >SELECT category, category, sort_order::text, parent >FROM node >WHERE parent IS NULL > UNION ALL >SELECT t.path || '.' || n.category, > n.category, > t.sort_order || '.' || n.sort_order, > n.parent >FROM tree t >JOIN node n ON n.parent = t.category > ) > SELECT path > FROM tree > ORDER BY sort_order May be, but then you're just re-inventing ltree again. I'm pretty sure this must be possible without adding convoluted things like casting sort orders to text (which can for example cause issues like '10' ending up between '1' and '2'). Since this is 8.4 anyway (CTE's after all), can't the sorting be done using a windowing function or something? We have recursion now, there's got to be a proper solution, I just can't get my mind around it right now. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4bcdf5a610412270627163! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ltree - how to sort nodes on parent node
On 20 Apr 2010, at 11:59, cojack wrote: >> 1). The way you're doing this in your new examples should work, although >> I'd probably make the ordering numbers part of the category names and >> split those off when I read them. For example: >> 27 | 1|Top >> 28 | 1|Top.1|Science >> 29 | 1|Top.2|Hobby >> 30 | 1|Top.3|Colors >> 31 | 1|Top.1|Science.1|Physics >> 32 | 1|Top.1|Science.2|Chemistry >> 33 | 1|Top.1|Science.3|Biology >> 34 | 1|Top.1|Science.4|History >> 35 | 1|Top.2|Hobby.1|Fishing >> 36 | 1|Top.2|Hobby.2|Football >> 37 | 1|Top.3|Colors.1|Black >> 38 | 1|Top.3|Colors.2|Red >> 39 | 1|Top.3|Colors.3|Blue >> 40 | 1|Top.1|Science.5|Archeology >> 41 | 1|Top.2|Hobby.3|Swimming >> 42 | 1|Top.3|Colors.4|Gray >> 43 | 1|Top.3|Colors.5|Purple >> 44 | 1|Top.3|Colors.6|Brown >> 45 | 1|Top.2|Hobby.4|Climbing > My and your first example doesn't work fine at all, why? Becouse when we add > more thank 10 sub nodes in some node, the 10 node will not be after 9, but That's just a matter of reserving enough padding for the numbers to fit. It does mean you bake in an upper limit to the number of items people can sort, but there is a practical limit your users are very unlikely to ever pass. I think anything past 4 digits is unlikely to happen. It's not a very clean solution, but it certainly does work. > after 1 before 2, and this is not good idea to set sort in path. I think the > best idea for this will be create other column, with also ltree data type > and stored inside a sort/ordering data. Like: > > 1 > 1.1 > 1.1.1 > 1.1.2 > 1.1.3 > > And while selected it from table, just cast it to int. I'll check this and > his performance after I return from work. This has the same problem as the previous one, 10 will end up between 1 and 2. It is cleaner than combining both into one tree though, so with sufficient padding it should work. > I am not interested about recursive queries, i think this kill ltree idea. And IMHO it should. ltree is from a time when we didn't have any other means to describe data organised as a tree in Postgres. Navigating a tree is inherently recursive, so recursion is most likely the proper way to go about it. A solution omitting recursion (like ltree) can be faster, but you will run into limitations like the one you're currently struggling with. A solution with recursive queries will probably be more flexible and allows for referential integrity without having to write your own triggers and stuff - for example, what happens if you decide that Archeology isn't a Science but a Colour? What makes sure it's child-nodes get moved into Colors as well? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4bcdf97810413554942613! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ltree - how to sort nodes on parent node
On Tue, Apr 20, 2010 at 1:58 PM, Alban Hertroys wrote: > On 20 Apr 2010, at 11:59, cojack wrote: > > >> I am not interested about recursive queries, i think this kill ltree idea. > > > And IMHO it should. ltree is from a time when we didn't have any other means > to describe data organised as a tree in Postgres. Navigating a tree is > inherently recursive, so recursion is most likely the proper way to go about > it. > > A solution omitting recursion (like ltree) can be faster, but you will run > into limitations like the one you're currently struggling with. > > A solution with recursive queries will probably be more flexible and allows > for referential integrity without having to write your own triggers and stuff > - for example, what happens if you decide that Archeology isn't a Science but > a Colour? What makes sure it's child-nodes get moved into Colors as well? > I've only been peripherally following this thread, so the following may be overkill for the requirements, but the non-recursive / flat query, solution is usually the set / subset pattern. It's been popularized by Joe Celko and he has gone as far as writing a book on the topic "Trees and hierarchies in SQL for smarties". If you don't have many requirements for reordering the tree this solution works well. It can be more of a pain if you need a GUI for tree management (but can be done). We use this type of solution to manage trees up to about 100,000 nodes in size with good performance. Other non-recursive solutions include Vadim Tropashko's (now with Oracle) Nested Interval Tree Encoding methods, which map directly to the dotted path (1.1.3) type tree notations in the examples in this thread and are a variation on the set / subset models. -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can the log_statement parameter be set at the user level?
Can the log_statement configuration parameter be set at the user level? For auditing purposes I need to capture certain statements being executed by specific accounts. However, there are some accounts that it would be nice not to be logging this information nor is it required. If log_statement can be set at the user level, then I can set it to not log 'mod' for the accounts that I do not want this detail. Thanks, Sandra Arnold Sr. Database Administrator NCI DOE/OSTI Oak Ridge, TN
Re: [GENERAL] Ltree - how to sort nodes on parent node
In article <1f96e061-713c-4929-a7d9-278e5b608...@solfertje.student.utwente.nl>, Alban Hertroys writes: > On 20 Apr 2010, at 18:05, Harald Fuchs wrote: >> Here's a working version: >> >> WITH RECURSIVE tree (path, category, sort_order, parent) AS ( >> SELECT category, category, sort_order::text, parent >> FROM node >> WHERE parent IS NULL >> UNION ALL >> SELECT t.path || '.' || n.category, >> n.category, >> t.sort_order || '.' || n.sort_order, >> n.parent >> FROM tree t >> JOIN node n ON n.parent = t.category >> ) >> SELECT path >> FROM tree >> ORDER BY sort_order > May be, but then you're just re-inventing ltree again. Not quite - with proper normalization you're storing the path elements only once and create the ltree-style paths on the fly. > I'm pretty sure this must be possible without adding convoluted > things like casting sort orders to text (which can for example cause > issues like '10' ending up between '1' and '2'). Ah, you're right. I think _some_ convolution is still needed because we must remember the sort order for each path element. > Since this is 8.4 anyway (CTE's after all), can't the sorting be > done using a windowing function or something? We have recursion now, > there's got to be a proper solution, I just can't get my mind around > it right now. I don't think windowing functions will help here. Anyway, here's a complete example which also deals with the 1/10/2 issue you mentioned above: CREATE TABLE node ( id serial NOT NULL, category text NOT NULL, sort_order int NOT NULL, parent int NULL REFERENCES node (id), PRIMARY KEY (id) ); CREATE UNIQUE INDEX node_pc_uq ON node (parent, category); -- Enforce unambiguous sorting CREATE UNIQUE INDEX node_ps_uq ON node (parent, sort_order); COPY node (id, category, sort_order, parent) FROM stdin; 1 Top 1 \N 2 Science 1 1 3 Physics 1 2 4 Chemistry 2 2 5 Biology 3 2 6 History 4 2 7 Archeology 5 2 8 Hobby 2 1 9 Fishing 1 8 10 Football2 8 11 Swimming3 8 12 Climbing4 8 13 Colors 3 1 14 Black 1 13 15 Red 2 13 16 Blue3 13 17 Gray4 13 18 Purple 5 13 19 Brown 6 13 \. WITH RECURSIVE tree (path, id, sort_order, parent) AS ( SELECT category, id, ARRAY[sort_order], parent FROM node WHERE parent IS NULL UNION ALL SELECT t.path || '.' || n.category, n.id, t.sort_order || n.sort_order, n.parent FROM tree t JOIN node n ON n.parent = t.id ) SELECT path, id, sort_order, parent FROM tree ORDER BY sort_order; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to terminate a query
Hi there! select * from pg_stat_activity; shows me a select my_function() query that has been running for too long. How do I kill it? kill -9 of the procpid seems to kill the entire server process. So I'm not really comfortable with that. The query was started by a webscript that was closed a long time ago. So how do you stop them? (This is on a Linux box) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rollback Failure
Hi all, I have a transaction where I get a rollback failure if a sql error is returned. I dont know why the rollback fails!! Does anyone have any suggestions or information about this case? Thanks & B.R, Mai Fawzy
[GENERAL] Re: [ADMIN] Can the log_statement parameter be set at the user level?
"Arnold, Sandra" wrote: > Can the log_statement configuration parameter be set at the user level? ALTER USER userid SET log_statement = 'none'; -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rollback Failure
On Tue, Apr 20, 2010 at 2:54 PM, mai fawzy wrote: > Hi all, > I have a transaction where I get a rollback failure if a sql error is > returned. I dont know why the rollback fails!! > Does anyone have any suggestions or information about this case? Got a simple test case that can show it? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] Can the log_statement parameter be set at the user level?
On Tue, Apr 20, 2010 at 1:23 PM, Arnold, Sandra wrote: > Can the log_statement configuration parameter be set at the user level? For > auditing purposes I need to capture certain statements being executed by > specific accounts. However, there are some accounts that it would be nice > not to be logging this information nor is it required. If log_statement can > be set at the user level, then I can set it to not log 'mod' for the > accounts that I do not want this detail. Yep. As Kevin mentioned, it can be set by role / user. Also by database and by cluster. User overrides db, db overrides cluster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to terminate a query
On Tue, Apr 20, 2010 at 2:42 PM, A B wrote: > Hi there! > > select * from pg_stat_activity; > shows me a > select my_function() > query that has been running for too long. > How do I kill it? select pg_cancel_backend(pid); will kill a running query. > kill -9 of the procpid seems to kill the entire server process. So kill -9 is NOT recommended. Sometimes cancel_backend fails because there's no point where the code checks for a cancel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance impact of log streaming replication
What is the expected performance impact of the log streaming replication in 9.0? In the past I've used the log shipping replication of MySQL and it caused the performance of the master to drop by almost 50%. Just wondered if Postgresql's replication is expected to behave similarly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance impact of log streaming replication
What is the expected performance impact of the log streaming replication in 9.0? In the past I've used the log shipping replication of MySQL and it caused the performance of the master to drop by almost 50%. Just wondered if Postgresql's replication is expected to behave similarly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance impact of log streaming replication
On Tue, Apr 20, 2010 at 3:48 PM, Andy wrote: > What is the expected performance impact of the log streaming replication in > 9.0? > > In the past I've used the log shipping replication of MySQL and it caused the > performance of the master to drop by almost 50%. Just wondered if > Postgresql's replication is expected to behave similarly. Whoa! Sounds like a slow disk IO subsystem, or the inability to take advantage of a fast one. PostgreSQL's PITR replication is already a working part of pgsql. Hot stanbys are due in 9.0, and should produce no more load than they already do in 8.4 or 8.3. Have you tried setting up PITR on pg 8.4 to see how it impacts your server's performance? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance impact of log streaming replication
> Whoa! Sounds like a slow disk IO subsystem, or the > inability to take > advantage of a fast one. > > PostgreSQL's PITR replication is already a working part of > pgsql. Hot > stanbys are due in 9.0, and should produce no more load > than they > already do in 8.4 or 8.3. Have you tried setting up > PITR on pg 8.4 to > see how it impacts your server's performance? > No I haven't. I'm using MySQL right now. But I want to learn more about Postgresql's Hot Standby and see if it offers a better replication solution. Can anyone share their experience about Postgresql replication performance impact? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
On 20/04/2010 10:33 PM, Scott Marlowe wrote: psql show log_statement; log_statement --- all Note however that other sessions won't see the change. Only connections that come after the change will see it. Also, as the OP wants to use it for auditing, it's worth noting that it's trivial for the audited user to simply disable log_statement in a session with a SET command. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance impact of log streaming replication
On 21/04/2010 5:15 AM, Andy wrote: What is the expected performance impact of the log streaming replication in 9.0? In the past I've used the log shipping replication of MySQL and it caused the performance of the master to drop by almost 50%. Just wondered if Postgresql's replication is expected to behave similarly. Pg already writes the logs required as part of normal operation. Most likely that performance drop with MySQL was due to the extra disk I/O of logging activity, which you shouldn't see with Pg. All streaming replication adds in performance terms should be some network I/O and the need to keep those logs around until the slave has received them. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
Craig Ringer wrote: On 20/04/2010 10:33 PM, Scott Marlowe wrote: psql show log_statement; log_statement --- all Note however that other sessions won't see the change. Only connections that come after the change will see it. Also, as the OP wants to use it for auditing, it's worth noting that it's trivial for the audited user to simply disable log_statement in a session with a SET command. From the docs for log_statement: "Only superusers can change this setting. " Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help with tracking!
On 21/04/2010 8:10 AM, Steve Crawford wrote: From the docs for log_statement: "Only superusers can change this setting. " Argh. Thankyou. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Performance impact of log streaming replication
Andy wrote: What is the expected performance impact of the log streaming replication in 9.0? In the past I've used the log shipping replication of MySQL and it caused the performance of the master to drop by almost 50%. Just wondered if Postgresql's replication is expected to behave similarly. Should only be in the low single digit percentages, except in some unusual cases--for example, there's an optimization for creating a new table and populating it all in one transaction that has to be disabled when replication is turned on, so that particular operation can be much slower. MySQL replication works by shipping a binary log of statements around, and that's claimed to have a similar overhead: about 1%: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html However, its performance is sensitive to whether that logging is going to a high performance disk or not. It's common for people to throw those onto network shares and the like, which can cripple the master if you do that badly. The built-in replication in PostgreSQL saves log files of disk block changes instead, ones that are already being created by the database anyway for crash recovery. The only additional overhead beyond standard operation is copying those files somewhere else--you're always paying most of the logging overhead all the time in standard, unreplicated PostgreSQL. The whole thing is quite fast and robust, without any weird limitations like those listed at http://dev.mysql.com/doc/refman/5.0/en/replication-features.html The main downside of the approach taken in PostgreSQL compared to what MySQL does is that the slaves are not as decoupled from what the master does in Postgres, which makes it harder to get scale-out replication going still. You can certainly do it right now, it's just harder than most people would like it to be to setup. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tar error, in pg_start_backup()
Hi All,
Thank you Magnus, Bruce...!!
Regards
Raghavendra
On Tue, Apr 20, 2010 at 7:20 PM, Bruce Momjian wrote:
> Magnus Hagander wrote:
> > .
> >
> > On Fri, Apr 16, 2010 at 11:55 AM, raghavendra t
> > wrote:
> > > Hi All,
> > >
> > > For?some setups reason, i started taking Hot backup. In this course I
> have first issued pg_start_backup('backup') and went to the data directory
> for backing up in OS format using the command "tar -cf backup.tar? /data" .
> When i issued this command , tar was generating some errors as show below.
> > >
> > > bash-3.00# tar -cf 16aprilstandby.tar /db-data/
> > > tar: Removing leading `/' from member names
> > > tar: /db-data/base/24643/151667: file changed as we read it
> > > tar: /db-data/base/24643/151764.2: file changed as we read it
> > > tar: /db-data/base/24643/151766: file changed as we read it
> > > tar: /db-data/base/24643/151768: file changed as we read it
> > > tar: /db-data/base/66412/151969: file changed as we read it
> > >
> > > After sometime tar has ended and i also issued pg_stop_backup() and i
> continued the process.
> > >
> > > My question here is, is this errors generated by tar are to worrisome
> or whats happening in the backend.
> > > Is "tar" file is safewell to use. Could you please tell me.
> >
> > Those are not errors, they are warnings. As long as you use
> > pg_start_backup() and pg_stop_backup() before and after the tar, they
> > are perfectly harmless, and can be ignored.
>
> Yes, and the manual does mention this issue:
>
>
> http://developer.postgresql.org/pgdocs/postgres/continuous-archiving.html#BACKUP-BASE-BACKUP
>
>Some file system backup tools emit warnings or errors if the files
> they
>are trying to copy change while the copy proceeds. When taking a
> base
>backup of an active database, this situation is normal and not an
> error.
>
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
[GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)
Recently, I am facing problem for not able to start the PostgreSQL service under one of my machine : Windows XP/Professional/Service Pack3 It had been running no problem previously. I try to run the following command : D:\Program Files\PostgreSQL\8.4\bin>pg_ctl.exe runservice -N "postgresql-8.4" -D "D:/Program Files/PostgreSQL/8.4/data" -w pg_ctl: could not start service "postgresql-8.4": error code 1063 I follow the suggestion http://www.mail-archive.com/pgsql-general@postgresql.org/msg83261.html, by running LSPFix.exe. Doesn't help either. I disable all the firewall, run an antivirus to scan for malware... not work still. Any suggestion on how I can debug this problem? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)
And the log message (D:\Program Files\PostgreSQL\8.4\data\pg_log) is as follow : 2010-04-21 13:34:58 MYTLOG: database system was interrupted; last known up at 2010-04-08 14:26:26 MYT 2010-04-21 13:34:58 MYTLOG: could not open file "pg_xlog/000100090041" (log file 9, segment 65): No such file or directory 2010-04-21 13:34:58 MYTLOG: invalid primary checkpoint record 2010-04-21 13:34:58 MYTLOG: could not open file "pg_xlog/00010009003E" (log file 9, segment 62): No such file or directory 2010-04-21 13:34:58 MYTLOG: invalid secondary checkpoint record 2010-04-21 13:34:58 MYTPANIC: could not locate a valid checkpoint record This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2010-04-21 13:34:58 MYTLOG: startup process (PID 3452) exited with exit code 3 2010-04-21 13:34:58 MYTLOG: aborting startup due to startup process failure Thanks and Regards Yan Cheng CHEOK --- On Wed, 4/21/10, Yan Cheng CHEOK wrote: > From: Yan Cheng CHEOK > Subject: unable to start postgresql server services (Windows > XP/Professional/Service Pack3) > To: pgsql-general@postgresql.org > Date: Wednesday, April 21, 2010, 10:04 AM > Recently, I am facing problem for not > able to start the PostgreSQL service under one of my machine > : > > Windows XP/Professional/Service Pack3 > > It had been running no problem previously. > > I try to run the following command : > > D:\Program Files\PostgreSQL\8.4\bin>pg_ctl.exe > runservice -N "postgresql-8.4" -D "D:/Program > Files/PostgreSQL/8.4/data" -w > pg_ctl: could not start service "postgresql-8.4": error > code 1063 > > I follow the suggestion > http://www.mail-archive.com/pgsql-general@postgresql.org/msg83261.html, > by running LSPFix.exe. Doesn't help either. > > I disable all the firewall, run an antivirus to scan for > malware... not work still. > > Any suggestion on how I can debug this problem? > > Thanks and Regards > Yan Cheng CHEOK > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)
Using the command solve the problem. Bravo! D:\Program Files\PostgreSQL\8.4\bin>pg_resetxlog -f "D:\Program Files\PostgreSQL\8.4\data" Transaction log reset Thanks and Regards Yan Cheng CHEOK --- On Wed, 4/21/10, Yan Cheng CHEOK wrote: > From: Yan Cheng CHEOK > Subject: Re: unable to start postgresql server services (Windows > XP/Professional/Service Pack3) > To: pgsql-general@postgresql.org > Date: Wednesday, April 21, 2010, 1:40 PM > And the log message (D:\Program > Files\PostgreSQL\8.4\data\pg_log) is as follow : > > > 2010-04-21 13:34:58 MYTLOG: database system was > interrupted; last known up at 2010-04-08 14:26:26 MYT > 2010-04-21 13:34:58 MYTLOG: could not open file > "pg_xlog/000100090041" (log file 9, segment 65): > No such file or directory > 2010-04-21 13:34:58 MYTLOG: invalid primary > checkpoint record > 2010-04-21 13:34:58 MYTLOG: could not open file > "pg_xlog/00010009003E" (log file 9, segment 62): > No such file or directory > 2010-04-21 13:34:58 MYTLOG: invalid secondary > checkpoint record > 2010-04-21 13:34:58 MYTPANIC: could not locate a > valid checkpoint record > > > This application has requested the Runtime to terminate it > in an unusual way. > Please contact the application's support team for more > information. > > 2010-04-21 13:34:58 MYTLOG: startup process (PID > 3452) exited with exit code 3 > 2010-04-21 13:34:58 MYTLOG: aborting startup due to > startup process failure > > > Thanks and Regards > Yan Cheng CHEOK > > > --- On Wed, 4/21/10, Yan Cheng CHEOK > wrote: > > > From: Yan Cheng CHEOK > > Subject: unable to start postgresql server services > (Windows XP/Professional/Service Pack3) > > To: pgsql-general@postgresql.org > > Date: Wednesday, April 21, 2010, 10:04 AM > > Recently, I am facing problem for not > > able to start the PostgreSQL service under one of my > machine > > : > > > > Windows XP/Professional/Service Pack3 > > > > It had been running no problem previously. > > > > I try to run the following command : > > > > D:\Program Files\PostgreSQL\8.4\bin>pg_ctl.exe > > runservice -N "postgresql-8.4" -D "D:/Program > > Files/PostgreSQL/8.4/data" -w > > pg_ctl: could not start service "postgresql-8.4": > error > > code 1063 > > > > I follow the suggestion > > http://www.mail-archive.com/pgsql-general@postgresql.org/msg83261.html, > > by running LSPFix.exe. Doesn't help either. > > > > I disable all the firewall, run an antivirus to scan > for > > malware... not work still. > > > > Any suggestion on how I can debug this problem? > > > > Thanks and Regards > > Yan Cheng CHEOK > > > > > > > > > > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to terminate a query
> select pg_cancel_backend(pid); > will kill a running query. Thanks. > Sometimes cancel_backend fails because there's no point where the code > checks for a cancel. What would such a point in the code look like? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to start postgresql server services (Windows XP/Professional/Service Pack3)
On Tue, Apr 20, 2010 at 11:47 PM, Yan Cheng CHEOK wrote: > Using the command solve the problem. Bravo! > > D:\Program Files\PostgreSQL\8.4\bin>pg_resetxlog -f "D:\Program > Files\PostgreSQL\8.4\data" Just so you know you may (likely will) lose some transactions. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tablespace Problem
Sir, I have found a problem in my database. The following error occurs sometime not any specific database operation. ERROR:XX000:count not create temporary file "pg_tblspc/86014/pgsql_tmp/pgsql_tmp3568.0":No such file or directory. The above error received from the database, but not affect any database operation. Please help me to rectify from the error and kindly explain the reason for the error. Thanks. Regards Gloier
